Oracle常用语句合集

发表于 | 分类于

表空间操作

1、创建表空间

1
2
3
4
create tablespace asom nologging datafile
 'D:\oracle\product\10.2.0\oradata\orcl\asom01.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom02.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom03.dbf' size 1000M autoextend on next 100M maxsize 20G

2、新增表空间数据文件

1
2
3
4
5
6
alter tablespace asom add datafile 
 'D:\oracle\product\10.2.0\oradata\orcl\asom04.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom05.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom06.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom07.dbf' size 1000M autoextend on next 100M maxsize 20G,
 'D:\oracle\product\10.2.0\oradata\orcl\asom08.dbf' size 1000M autoextend on next 100M maxsize 20G

3、查询表空间对应的数据文件

1
select tablespace_name,file_id,bytes,file_name from dba_data_files

4、查询表空间利用率

1
2
3
4
5
6
7
8
9
SELECT a.tablespace_name as tablespace_name,
       to_char(b.total/1024/1024,999999.99) as Total,
       to_char((b.total-a.free)/1024/1024,999999.99) as Used,
       to_char(a.free/1024/1024,999999.99) as Free,
       to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
     (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY a.tablespace_name;

5、查询各表的表空间使用情况

1
2
3
4
5
6
7
select t.table_name,t.tablespace_name,t.num_rows,a.bytes/1024/1024 "物理空间(M)", 
	round(t.num_rows*t.avg_row_len/1024/1024,2) "实际使用(M)",
	a.bytes/1024/1024-round(t.num_rows*t.avg_row_len/1024/1024,2) "差值(M)"
from all_tables t,user_segments a
where t.owner='ASOM' /*and t.table_name like 'MTR%'*/ and t.num_rows>200*10000 
	and t.table_name=a.segment_name and a.segment_type = 'TABLE'
order by t.table_name;

6、释放表空间

1
2
3
4
alter table TABLE_NAME enable row movement;
alter table TABLE_NAME shrink space;

alter table TABLE_NAME shrink space cascade;   索引也能缩小，效率很低，慎重。

7、导入表时指定表空间

1
2
3
4
5
6
7
8
9
10
11
SQL> create user user01 identified by password default tablespace ts01;

SQL> grant resource,connect to user01;

SQL> grant dba to user01;//赋DBA权限

SQL> revoke unlimited tablespace from user01;//撤销此权限

SQL> alter user user01 quota 0 on system;//将用户在System表空间的配额置为0

SQL> alter user user01 quota unlimited on ts01;//设置在用户在myhuang表空间配额不受限。

经过上述设置后，就可以用imp导入数据，数据将会进入指定的ts01表空间



物化视图操作

1、创建物化视图

1
2
3
4
5
create materialized view V_MTR_STATIONEQU
refresh force on demand
start with to_date('04-06-2013 10:27:06', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 3/(24*6) 
as
select * from table_name;

2、手动刷新物化视图

1
exec dbms_mview.refresh('v_mtr_stationequ');

3、删除物化视图

1
drop materialized view V_MTR_STATIONEQU