一、表空间管理
1、获取表空间DDL
--获得单个表空间的DDL: select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) |
2、创建临时表空间
create temporary tablespace test_temp tempfile '/oracle/oradata/temp01.dbf' size 1024m autoextend on next 500m maxsize 30G extent management local; |
3、创建数据表空间
create tablespace test_data logging datafile '/oracle/oradata/data01.dbf' size 1024m autoextend on next 500m maxsize 30G extent management local; |
4、添加表空间
Alter tablespace test_data add datafile ‘/oracle/oradata/oa_data02.dbf’ size 10G maxsize 30G autoextend on; Alter tablespace test_data add datafile ‘+DATA’ size 10G autoextend on; |
1、删除表空间
5.1、正常方式删除表空间
--删除空的表空间，但是不包含物理文件 drop tablespace tablespace_name; --删除非空表空间，但是不包含物理文件 drop tablespace tablespace_name including contents; --删除空表空间，包含物理文件 drop tablespace tablespace_name including datafiles; --删除非空表空间，包含物理文件 drop tablespace tablespace_name including contents and datafiles; --如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS; |
5.2、基于等待事件10851强行删除表空间
alter session set events '10851 trace name context forever,level 1'; drop table SRM.AQ$_FND_REQUEST_QUEUE_TABLE_H; drop tablespace SRM_DATA including contents and datafiles; alter session set events '10851 trace name context off'; |
6、查看表空间数据文件
select file_id,tablespace_name,file_name,bytes/1024/1024/1024 GB,autoextensible from dba_data_files UNION ALL select file_id,tablespace_name,file_name,bytes/1024/1024/1024 GB,autoextensible from dba_temp_files; |
7、查看表空间大小
--1、查看表空间使用情况 set linesize 200 set pages 2000 col TABLESPACENAME for a30 SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName, round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)", round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)", round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)", ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%", round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)", ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%" FROM dba_data_files a, (SELECT SUM(NVL(bytes,0)) free_space1, file_id FROM dba_free_space GROUP BY file_id ) b WHERE a.file_id = b.file_id(+) GROUP BY a.TABLESPACE_NAME ORDER BY "Used_percent%" desc; --2、查看临时表空间使用率 select c.tablespace_name, to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb, to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb, to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb, to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_temp_files GROUP by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool GROUP by tablespace_name) d where c.tablespace_name = d.tablespace_name; |
8、查询当前正在消耗temp空间的sql语句
select a.username, a.sql_id, a.SEGTYPE, b.BYTES_USED/1024/1024/1024 "temp_used(G)", b.BYTES_FREE/1024/1024/1024 "temp_freeG)" from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE = b.tablespace_name; 说明： segtype :正在执行的SQL语句做的是什么操作 temp_used(G) :正在执行sql语句使用的临时表空间的大小 temp_free(G) :剩余多少临时表空间 |
9、重建temp表空间
--1、创建中转临时表空间 create temporary tablespace TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; --2、改变缺省临时表空间为刚刚创建的新临时表空间temp1 alter database default temporary tablespace temp1; --3、删除原来临时表空间 drop tablespace temp including contents and datafiles; --4、重新创建临时表空间 create temporary tablespace TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; --5、重置缺省临时表空间为新建的temp表空间 alter database default temporary tablespace temp; --6、删除中转用临时表空间 drop tablespace temp1 including contents and datafiles; --7、如果有必要，那么重新指定用户表空间为重建的临时表空间 alter user arbor temporary tablespace temp; |
10、统计每个用户表空间使用率
SELECT c.owner, a.tablespace_name, total/1024/1024/1024 "tb_total(G)", free/1024/1024/1024 "tb_free(G)", (total-free )/1024/1024/1024 "tb_used(G)", Round((total - free)/total,4)*100 "tb_used_percent(%)", c.schemas_use/1024/1024 "user_used(M)", round((schemas_use)/total,4)*100 "user_used_percent(%)" 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, (Select owner ,Tablespace_Name, Sum(bytes) schemas_use From Dba_Segments Group By owner,Tablespace_Name) c WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name =c.Tablespace_Name order by c.owner； |
11、统计表空间每日增长业务量
依靠dba_hist_tbspc_space_usage视图来获得表空间的每日增长量
select a.name, b.* from v$tablespace a, (select tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime, round(max(tablespace_usedsize * 8 / 1024), 2) "used_size(MB)", round(max(tablespace_usedsize * 8 / 1024 / 1024), 2) "used_size(GB)" from dba_hist_tbspc_space_usage where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) > trunc(sysdate - 17) group by tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) order by tablespace_id, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b where a.ts# = b.tablespace_id; |
12、估算oracle数据库及数据库对象历史增长情况
在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图，该视图记录了快照时间内segment-level段级的历史统计信息
--1、查看数据库历史增长情况（不含undo和temp） with tmp as (select rtime, sum(tablespace_usedsize_kb) tablespace_usedsize_kb, sum(tablespace_size_kb) tablespace_size_kb from (select rtime, e.tablespace_id, (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb, (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb from dba_hist_tbspc_space_usage e, dba_tablespaces f, v$tablespace g where e.tablespace_id = g.TS# and f.tablespace_name = g.NAME and f.contents not in ('TEMPORARY','UNDO')) group by rtime) select tmp.rtime, tablespace_usedsize_kb, tablespace_size_kb, (tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB from tmp, (select max(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2 where t2.rtime = tmp.rtime; --2、查看数据库历史增长情况（含undo和temp） with tmp as (select min(rtime) rtime, sum(tablespace_usedsize_kb) tablespace_usedsize_kb, sum(tablespace_size_kb) tablespace_size_kb from (select rtime, e.tablespace_id, (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb, (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb from dba_hist_tbspc_space_usage e, dba_tablespaces f, v$tablespace g where e.tablespace_id = g.TS# and f.tablespace_name = g.NAME) group by rtime) select tmp.rtime, tablespace_usedsize_kb, tablespace_size_kb, (tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB from tmp, (select min(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2 where t2.rtime = tmp.rtime --3、列出相关段对象在快照时间内的使用空间的历史变化信息 select obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') start_day, sum(a.db_block_changes_delta) block_increase from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj where sn.snap_id = a.snap_id and obj.object_id = a.obj# and obj.owner not in ('SYS', 'SYSTEM') and end_interval_time between to_timestamp('01-MAY-2019', 'DD-MON-RRRR') and to_timestamp('05-MAY-2019', 'DD-MON-RRRR') group by obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') order by obj.owner, obj.object_name; --4、表空间增长量和增长率 SELECT A.NAME, B.TABLESPACE_ID, B.DATETIME, B.USED_SIZE_MB, B.INC_MB, CASE WHEN SUBSTR(INC_RATE, 1, 1) = '.' THEN '0' || INC_RATE WHEN SUBSTR(INC_RATE, 1, 2) = '-.' THEN '-0' || SUBSTR(INC_RATE, 2, LENGTH(INC_RATE)) ELSE INC_RATE END AS INC_RATEX FROM V$TABLESPACE A, (SELECT TABLESPACE_ID, DATETIME, USED_SIZE_MB, (DECODE(PREV_USE_MB, 0, 0, USED_SIZE_MB) - PREV_USE_MB) AS INC_MB, TO_CHAR(ROUND((DECODE(PREV_USE_MB, 0, 0, USED_SIZE_MB) - PREV_USE_MB) / DECODE(PREV_USE_MB, 0, 1, PREV_USE_MB) * 100, 2)) || '%' AS INC_RATE FROM (SELECT TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) DATETIME, MAX(TABLESPACE_USEDSIZE * 8 / 1024) USED_SIZE_MB, LAG(MAX(TABLESPACE_USEDSIZE * 8 / 1024), 1, 0) OVER(PARTITION BY TABLESPACE_ID ORDER BY TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss'))) AS PREV_USE_MB FROM DBA_HIST_TBSPC_SPACE_USAGE WHERE TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30) GROUP BY TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')))) B WHERE A.TS# = B.TABLESPACE_ID ORDER BY B.TABLESPACE_ID, DATETIME; |
13、检查表空间碎片（check_tbs_exces_fragment）
当我们碰到明明表空间还有10G剩余，为什么出现表/索引无法扩展的情况？
通过下面检测表空间碎片是否过度的脚本可以告诉你，虽然实际上表空间剩余容量虽然有10G那么大，但有可能实际并没有一块空间是可以满足next extend去扩展的。
--- Check tablespace for excessive fragmentation --- Author : Huai Xiaoming --- t1_filter是用来筛选表空间的，剩余空间绰绰有余的表空间可以不用检测 SET LINES 200 PAGES 200 HEAD ON FEEDBACK OFF AUTOT OFF TI OFF TIMI OFF col mes for a100 with t1 as (select TABLESPACE_NAME, round(USED_SPACE/128) mb, round(TABLESPACE_SIZE/128) totalmb, round(USED_PERCENT,2) USED_PERCENT from dba_tablespace_usage_metrics) ,t1_filter as (select * from t1 where totalmb-mb<100*power(2,10) and USED_PERCENT>90) ,t2 as (select fs.tablespace_name, fs.bytes/power(2,20) mb, count(*) cnt from DBA_FREE_SPACE fs, t1_filter t1f where --fs.tablespace_name like 'TBS%' and fs.tablespace_name= t1f.tablespace_name group by fs.tablespace_name, bytes/power(2,20)) ,t3 as (select tablespace_name, case when mb<1 then '<1MB' when mb<4 then '<4MB' else '>=4MB' end size_range, sum(mb) mb, sum(cnt) cnt from t2 group by tablespace_name, case when mb<1 then '<1MB' when mb<4 then '<4MB' else '>=4MB' end) ,t4 as (select tablespace_name, size_range, mb, cnt, ratio_to_report(mb)over(partition by tablespace_name) rtp from t3) , t5 as ( select tablespace_name,nvl(mb_less_1,0) mb_less_1,nvl(cnt_less_1,0) cnt_less_1,nvl(rtp_less_1,0) rtp_less_1, nvl(mb_less_4,0) mb_less_4,nvl(cnt_less_4,0) cnt_less_4,nvl(rtp_less_4,0) rtp_less_4, nvl(mb_more_4,0) mb_more_4,nvl(cnt_more_4,0) cnt_more_4,nvl(rtp_more_4,0) rtp_more_4 from ( select tablespace_name, max(case when size_range='<1MB' then size_range end) size_range_less_1, max(case when size_range='<1MB' then mb end) mb_less_1, max(case when size_range='<1MB' then cnt end) cnt_less_1, max(case when size_range='<1MB' then rtp end) rtp_less_1, max(case when size_range='<4MB' then size_range end) size_range_less_4, max(case when size_range='<4MB' then mb end) mb_less_4, max(case when size_range='<4MB' then cnt end) cnt_less_4, max(case when size_range='<4MB' then rtp end) rtp_less_4, max(case when size_range='>=4MB' then size_range end) size_range_more_4, max(case when size_range='>=4MB' then mb end) mb_more_4, max(case when size_range='>=4MB' then cnt end) cnt_more_4, max(case when size_range='>=4MB' then rtp end) rtp_more_4 from t4 group by tablespace_name) ) select 'Tablespace :'||tablespace_name ||'is no continuous space greater than or equal to 4M, or the total is less than 5G, and the table space usage rate exceeds 90%' mes from t5 where not(cnt_more_4>=1 and mb_more_4>5120); |
