oracle解锁表和用户,oracle数据库解锁表、删除用户和表空间等操作讲解-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:是否忽略创建错误