Oracle 表空间管理（一）
--表空间查询--郭
select tablespace_name,
round(megs_alloc,2) megs_alloc,
round(megs_used,2) megs_used,
--Pct_Free || '%' Pct_Free,
Pct_used || '%' Pct_used,
round(megs_free,2) megs_free,max megs_withMAX,
round((nvl(megs_used, 0) / max) * 100)||'%' used_withMax
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes / 1048576) Max
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes))/1048576 max
from sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY Pct_used desc)
where tablespace_name not like 'UNDO%';
--end--
--查表空间大小及使用--
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),
'990.99')||'%' "使用比(%)",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY "使用比(%)" DESC;
--结束--
--查询一个表空间里所有表占用空间大小
select segment_name, tablespace_name, sum(bytes / 1024 / 1024)
from dba_extents
where tablespace_name = 'TS_BILLING_DATA01'
group by segment_name, tablespace_name
order by 3 desc;
--end--
--查表空间使用率情况（含临时表空间）
SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.99'
) USE,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) "Used %"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.99') USE,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY';
--end--
--查看一个表空间里哪些表最大,
select t.owner,t.segment_name,t.tablespace_name,bytes/1024/1024/1024 as sizes,q.num_rows,t.segment_type
from dba_segments t
left join dba_tables q
on t.segment_name=q.table_name
and t.owner=q.owner
where t.segment_type='TABLE'
and t.tablespace_name='DEMO' --需要查看的表空间
order by 4 desc
----结束----
-- 查看库有哪些表空间
select tablespace_name,contents,status from dba_tablespaces;
--查一个表空间里有哪些表
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='表空间名大写';
--查一个表属于哪个表空间
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME='表名大写';
--查看当前用户表用的表空间
select distinct TABLESPACE_NAME from tabs;
--查看用户名下所有的表
select table_name from all_tables where owner=upper('SCOTT');
--查看所有用户的表空间
select name from v$tablespace;
select * from dba_tablespaces;
--查看表空间对应的表空间文件或裸设备
select tablespace_name,file_name,round(bytes/(1024*1024),0) total_space,AUTOEXTENSIBLE,increment_by,maxbytes from dba_data_files order by tablespace_name, file_name;
--查看表空间是否自动扩展
select t.FILE_NAME,t.AUTOEXTENSIBLE from Dba_Data_Files t;
select t.tablespace_name, d.file_name, d.autoextensible,d.bytes, d.maxbytes,d.status from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name
--查看表空间路径
select * from dba_data_files order by 3;
select name from v$datafile;
--创建表空间
create tablespace yang
logging
datafile '/data/oracle/app/oradata/orcl/yang.dbf'
size 100m
autoextend on 可以到这就行
next 100m maxsize 20480m
extent management local;
--表空间改名
ALTER tablespace 旧表空间 RENAME TO 新表空间名;
--删除表空间，同时删除数据文件：
drop tablespace test_data including contents and datafiles;
--创建用户并指定表空间
create user XXX identified by 123456
default tablespace XXX
temporary tablespace temp;
建一张表
create table yyyy (id int,name varchar2(10));
删除一张表
drop table 表名;
清空一张表
SQL> truncate table 表名;
添加字段
alter table 表名 add 字段名 varchar2(10);
添加表内容
insert into yyyy (id,name) values(1,'孙悟空');
insert into yyyy (id,name) values(2,'猪八戒');
insert into yyyy values(3,'沙河尚');
insert into yyyy values(4,'唐僧');
删除一行
delete from yyyy where id=4;
