oracle数据库相关的语句

一、表空间相关

1.查看表空间的使用情况

select a.tablespace_name,total,free,total-free used from(select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name)a,(select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name)b where a.tablespace_name = b.tablespace_name and a.tablespace_name='表空间名'

2.如果表空间已满，查看表空间中前30个最大的表

select * from (select segment_name,bytes,owner from dba_segments where owner = '用户名' order by bytes desc ) where rownum <= 30 

3.否则给表空间增加新的数据文件

alter tablespace 表空间名add datafile 'E:ORACLE表空间名_2.ORA' size 1000m autoextend on next 100m maxsize 32000M;

4.查看数据库中用户占用表空间的大小

select owner,tablespace_name,sum(bytes)/1024/1024 from dba_segments group by owner,tablespace_name;

5.删除用户和其下的所有对象

一般情况下要断开所有与该数据库连接的相关应用。然后：

select username,sid,serial#,STATUS from v$session where username='用户名';

alter system kill session 'sid,serial#'；

drop user 用户名cascade;

一般做到这已经可以删除用户及其下的所有对象了，如果还是不行，这时候就要放大招了：

select 'alter system kill session'||' '''||sid||','||serial#||''';',username,STATUS from v$session where username='用户名' and status like 'IN%';

再批量执行；

6.创建表空间（使用大数据格式）

CREATE BIGFILE TABLESPACE "表空间名" DATAFILE 'D:APPADMINISTRATORORADATAORCLORADB.DBF' SIZE 30G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

二、导入(imp)导出(exp)相关(迁库)

一般来说，我迁库的思路是这样的，先将每个用户的表结构导出来（包括空表），再导出用户表数据（备份用），再将需要的表数据导入新库。
我们用exp导出时发现少空表，这是因为oracle11g新增了一个参数：deferred_segment_creation，含义是段延迟创建，默认是true。
若此参数为true，当新建一张表table1且没有向其中插入数据，则此表不会立即分配extent,也就是说不会立即占据数据空间，只有当insert数据后 才分配数据空间。这样可以节省少量数据空间。我们可以这么解决

（1）查看空表

select table_name from user_tables where NUM_ROWS=0 or NUM_ROWS is null;

（2）生成语句

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or NUM_ROWS is null;

（3）command下执行；

（4）导出表结构（including 空表）

exp 用户名/密码@实例名 file=d:****.dmp owner=用户名 buffer=65536 indexes =y feedback=100000 rows=n compress=n log=d:***.log

如果只想导出部分表的相关数据则可以：

exp 用户名/密码@实例名 file=d:****.dmp  tables =(表1，表2...)buffer=65536  log=d:***.log

（5）将该dmp导入新库：

imp 用户名/密码@实例名 file=d:***.dmp log=d:**.log fromuser=用户名（原）touser=用户名（新） ignore=y buffer=65536

(6)现在表结构有了，开始导入相关的数据了。

在导入之前需要做下面几件事，暂时disabled触发器，暂时关闭相关表的关联关系。

触发器就在trigger下面变为disable;
select 'alter table '|| table_name ||' disable constraint '|| constraint_name||';' from user_constraints a where a.constraint_type='R'

导入的表可能很多，这是后可以转置以后粘贴就好:

select wm_concat(table_name)from user_tables order by table_name;

imp 用户名/密码@实例名 fromuser=原 touser=新 file=e:***.dmp ignore=y TABLES=(table1,table2, ……);

导入以后开启相关的约束；
触发器开启就在trigger下面变为compiler;   
select 'alter table '|| table_name ||' enable constraint '|| constraint_name||';' from user_constraints a where a.constraint_type='R'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!