oracle数据库解锁表、删除用户和表空间等操作讲解

oracle解锁表、删除用户和表空间等操作讲解

解锁表
select s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
from v$session s, v$lock l
where s.sid = l.sid
and s.username is not null
order by sid;
alter system kill session '191,19';
删除用户和表空间
drop user gxdisability cascade;
drop tablespace disability including contents and datafiles;
1：增加列
        alter table ohm_two_rescue add (ispoor number);
2：修改列
      alter table ohm_two_rescue rename column ispoor to is_poor;
3：删除
    alter table 表名 drop column 列名;
4：alter table cjrjz_proposer modify (ensure_cark nvarchar2(40));
alter table cjrjz_proposer modify (ensure_cark nvarchar2(40));
    
5：exp cmc/cmc@192.168.6.110:1521/orcl file=d:\cmc_20171001.dmp log=20171001.log
exp gxdisability/123456@192.168.6.110:1521/orcl tables=(t_temp_1,t_temp_12 ) file=d:\temp.dmp log=temp.log
exp gxdisability/123456@192.168.6.110:1521/orcl tables=(cjrjz_canlian_user) file=d:\disable201711061.dmp log=201711061.log
exp gxdisability/123456@192.168.6.110:1521/orcl tables=(cjrjz_canlian_user) file=d:\disable201711061.dmp log=201711061.log
exp disability/disability@192.168.6.106:1521/orcl tables=(cjrjz_proposer712zd,cjrjz_application712zd,cjrjz_bank_account712zd,cjrjz_guardian712zd,cjrjz_publicity712zd,cjrjz_app_enjoy_archives712zd) file=d:\disable201711061.dmp log=20180115-1712.log
imp gxdisability/123456@192.168.6.110:1521/orcl  file=d:\disable201711061.dmp log=201711061.log
imp gxdisability/123456@192.168.6.110:1521/orcl  file=d:\disability.dmp  
imp gxdisability/123456@192.168.6.110:1521/orcl  file=d:\disability.dmp full=y ignore=y
6：刪除重複的數據
delete  from cjrjz_application11 where (pro_card_code) in ( select pro_card_code from cjrjz_application11 group by pro_card_code having count(id) > 1) and rowid not in
       (select min(rowid) from cjrjz_application11 group by pro_card_code having count(*) > 1);
7：导出用户整个数据库
exp gxdisability/123456@192.168.6.110:1521/orcl owner=gxdisability file=f:/db/gxdisable.dmp
exp cmc/cmc@192.168.6.110:1521/orcl owner=cmc file=f:/db/gxcmc.dmp
exp gxcmc/gxcmc@192.168.6.116:1521/orcl owner=gxcmc file=f:/db/gxcmc.dmp
exp gxdisability/123456@192.168.6.110:1521/orcl owner=gxdisability file=f:/db/gxdisability.dmp
导入命令：imp 用户名/密码@数据库 fromuser=用户名 touser=用户名 file=d:\cu.dmp ignore=y
imp:命令类型  
cu/mycu@db:导入的数据库登陆(用户名/密码@数据库)  
fromuser:文件的指定用户
touser：指定导入到当前登录的数据库某个用户  
file:需要导入的数据文件  
ignore:是否忽略创建错误