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:是否忽略创建错误