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 Total.name "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 b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
类似近身保镖的小说group by b.name
) Total
WHERE Free.Tablespace_name = Total.name
select dbf.tablespace_name,
健康生活习惯alspace "总量(M)",
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
round((dfs.freespace / alspace),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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。