1.生成用户下所有表删除语句
select 'drop table '||table_name||';' as sqlscript from user_tables;
2.查看表空间使用情况
select a.tablespace_name,a.bytes/1024/1024 "sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
3.给表空间增加一个文件，并设置初始大小和自动扩展大小
alter tablespace 表空间名称 add datafile '新表空间文件名(包括路径)' size 表空间文件初始大小(例如10240M) autoextend on next 文件自增大小(100M) MAXSIZE UNLIMITED;
4.oracle锁表问题解决
--查询锁表的用户和session信息
select b.username, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;
--杀锁表进程
alter system kill session'sid,serial#';
5.oracle11g导入dmp库文件
impdp user/password@127.0.0.1:1521/orcl directory=my_dir dumpfile=RGZS20220210-1.DMP exclude=table:"in(\'SM_BUSILOG_DEFAULT\')" logfile=20220210RGZS.log remap_schema=user:dmpuser
exclude表示跳过该表
