oracle11g 表空间常用命令

正则表达式

select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,3)
from dual



查看表空间 对应 数据文件




查看用户 对应 表空间
SELECT * from dba_users



select table_name from user_tables; //当前用户的表

select table_name from all_tables; //所有用户的表

select table_name from dba_tables; //包括系统表

select table_name from dba_tables where owner='用户名'


创建表空间


create tablespace "TBL99" datafile '/a8root/app/oracle/oradata/tablespace/TBL99.tbl' size 20480M logging online permanent blocksize 8k extent management local autoallocate segment space management auto;


扩充表空间
ALTER TABLESPACE "TBL99" ADD DATAFILE '/a8root/app/oracle/oradata/dmbi/tablespace/TBL99_2.tbl' size 20480M



--ALTER TABLESPACE "TBS_NAME" ADD DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ex_data' SIZE 10240M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED


查看表空间 属性

select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files;



查看表空间

select tablespace_name, segment_name, round(sum(bytes/(1024*1024))) used
from dba_extents
where tablespace_name='DW_TBL01'
group by tablespace_name,segment_name
order by round(sum(bytes/(1024*1024))) desc;







SELECT https://www.360docs.net/doc/4d14965354.html, "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select https://www.360docs.net/doc/4d14965354.html,, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by https://www.360docs.net/doc/4d14965354.html,
) Total
WHERE Free.Tablespace_name = https://www.360docs.net/doc/4d14965354.html,





select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as "总块数",
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
round((dfs.freespace / dbf.totalspace),4) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)