|
1.创建表空间
CREATE TEMPORARY TABLESPACE IBASE4JTEMP
?????????TEMPFILE ‘G:apphannasongoradataorclIBASE4JTEMP.DBF‘
?????????SIZE 32M
?????????AUTOEXTEND ON
?????????NEXT 32M MAXSIZE UNLIMITED
?????????EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE IBASE4J
?????????LOGGING
?????????DATAFILE ‘G:apphannasongoradataorclIBASE4J.DBF‘
?????????SIZE 32M
?????????AUTOEXTEND ON
?????????NEXT 32M MAXSIZE UNLIMITED
?????????EXTENT MANAGEMENT LOCAL;
?
2.创建用户，并授权
create user sinosong identified by sinosong
default tablespace IBASE4J
temporary tablespace IBASE4JTEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to sinosong;
grant resource to sinosong;
--grant connect,resource,dba to sinosong;
-- Grant/Revoke system privileges
grant create any sequence to sinosong;
grant create any view to sinosong;
grant create procedure to sinosong;
grant create table to sinosong;
grant create tablespace to sinosong;
grant create trigger to sinosong;
grant export full database to sinosong;
grant import full database to sinosong;
grant unlimited tablespace to sinosong;
?
3.删除用户和用户对象
drop user sinosong cascade;
?
4.解决oracle11g，空表无法导出的问题
select ‘alter table ‘||table_name||‘ allocate extent;‘ from user_tables where num_rows=0 or num_rows is null；
?
5.有数据的字段调整
如number-->String
create table BIZ_RENTAL_FACTORING_KEY_2 as select * from BIZ_RENTAL_FACTORING_KEY ;
drop table... ?? ?create table...
insert into BIZ_RENTAL_FACTORING_KEY select * from BIZ_RENTAL_FACTORING_KEY_2;
若char-->varchar，获取时不能写*，需要带trim()。
?
6.解锁用户
dba用户登录plsql，直接勾选取消锁定；
或者通过sql执行：alter user sinosong account unlock
?
7.删除重复数据，仅保留一条
DELETE FROM table_name a WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.id = a.id and b.name=a.name);
1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断
select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录
DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);
3、查找表中多余的重复记录（多个字段）
select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from 表 a where (a.Id,a.seq) in (select Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from 表 a where (a.Id,seq having count(*)>1)
8.数据库的误删恢复
1、误删数据
还原表
1.打开Flash存储的权限
ALTER TABLE tableName ENABLE row movement;
2.把表还原到指定时间点
flashback table tableName to timestamp to_timestamp(‘2018-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘);--后面的参数为要还原的时间点
利用Oracle的快照进行查找某个时间点的数据，可以查询到指定的时间段的数据
select * from tableName AS OF TIMESTAMP? (SYSTIMESTAMP - INTERVAL ‘100‘ MINUTE)
或
select * from tableName as of timestamp to_timestamp(‘2018-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘);
?
2、误删表
select * from user_recyclebin;
FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
如果是删了或修改里面的数据，可以先建立一个快表将删除修改之前状态的数据找回到这个表中:
CREATE TABLE QUICK_TABLE AS SELECT * FROM TABLE_NAME AS OF TIMESTAMP SYSDATE-1/24;(1/24一小时之前，5/1440为5分钟之前，或者指定日期(‘2018-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘))
?
9.密码过期问题
alter user userXXX identified by xxx；
如果想设置密码不过期，可用管理员登陆，然后执行：
?ALTER?PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
?
想要更多请前往原作者链接：
误删恢复==》
https://blog.csdn.net/shiyu1157758655/article/details/79578009
用户密码过期==》
http://www.blogjava.net/freeman1984/archive/2013/04/23/398301.html
此随笔仅为整理笔记，若侵权请回复我会清理。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
