技术交流

表空间查询维护
日期:2022年09月27日

一、表空间分配的大小和真实使用空间查看:

CLEAR COLUMNS BREAKS COMPUTES
SET ECHO        OFF
SET FEEDBACK    ON
SET HEADING     ON
SET LINESIZE    160
SET PAGESIZE    2000
SET TIMING      OFF
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS BREAKS COMPUTES
COLUMN status                    FORMAT a9                 HEADING 'Status'
COLUMN name                      FORMAT a30                HEADING 'TS. Name'
COLUMN type                      FORMAT a15                HEADING 'TS Type'
COLUMN extent_mgt                FORMAT a10                HEADING 'Ext.Mgt.'
COLUMN segment_mgt               FORMAT a10                HEADING 'Seg.Mgt.'
COLUMN ts_size                   FORMAT 999,999,999        HEADING 'TS.Size (MB)'
COLUMN used                      FORMAT 999,999,999        HEADING 'Used (MB)'
COLUMN free                      FORMAT 999,999,999        HEADING 'Free (MB)'
COLUMN pct_used                  FORMAT 999                HEADING 'Pct.Used'
COLUMN pct_free                  FORMAT 999                HEADING 'Pct.FREE'
BREAK ON report
COMPUTE count OF type   ON report
COMPUTE sum OF ts_size  ON report
COMPUTE sum OF used     ON report
SELECT
    d.tablespace_name                                     name
  , d.status                                              status
  , d.contents                                            type
--  , d.extent_management                                   extent_mgt
  , d.segment_space_management                            segment_mgt
  , NVL(a.bytes, 0)/1024/1024                             ts_size
  , NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024           used
  , NVL(f.bytes, 0)/1024/1024                             free
  , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)   pct_used
  ,NVL(f.bytes/a.bytes*100,0)                             pct_free
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
  , d.contents                                          type
--  , d.extent_management                                 extent_mgt
  , d.segment_space_management                          segment_mgt
  , NVL(a.bytes, 0)/1024/1024                           ts_size
  , NVL(t.bytes, 0)/1024/1024                           used
  , NVL(a.bytes - NVL(t.bytes,0), 0)/1024/1024          free
  , NVL(t.bytes / a.bytes * 100, 0)                     pct_used
  ,NVL((a.bytes - NVL(t.bytes,0))/a.bytes*100,0)        pct_free
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'
ORDER BY
  8 desc
/


二、cdb 层面查看所有pdb空间:

set line 500

col DB_NAME for a10

SELECT nvl(t.name, 'CDB$ROOT') as DB_NAME,D.TABLESPACE_NAME,(SPACE - NVL(FREE_SPACE, 0)) used,

FREE_SPACE,ROUND((SPACE - NVL(FREE_SPACE, 0)) / ((SPACE - NVL(FREE_SPACE, 0))+FREE_SPACE),2)*100 use_ratio,

TOTAL_SPACE max_space FROM (SELECT TABLESPACE_NAME,con_id,ROUND(SUM(BYTES) / (1024 * 1024)) SPACE,

ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /(1024 * 1024)) TOTAL_SPACE,SUM(BLOCKS) BLOCKS

FROM containers(DBA_DATA_FILES) GROUP BY TABLESPACE_NAME, con_id) D,(SELECT TABLESPACE_NAME,con_id,

ROUND(SUM(BYTES) / (1024 * 1024)) FREE_SPACE FROM containers(DBA_FREE_SPACE) GROUP BY TABLESPACE_NAME, con_id) F,

v$pdbs t WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.CON_ID = F.CON_ID and F.con_id = t.CON_ID(+)

ORDER BY 1

/


三、ASM空间使用率:

select name,free_mb,total_mb from v$asm_disk;

select group_number,name,state,total_mb,free_mb from v$asm_diskgroup; 

select path,total_mb,free_mb from v$asm_disk_stat;


四、数据文件表空间

1,查看数据文件大小及路径

SQL> select name from v$datafile;

select * from dba_tablespaces;

set line 300

col FILE_NAME for a50

col TABLESPACE_NAME for a10

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 from dba_data_files;   不显示容器数据库

2,创建数据文件表空间

create tablespace pdms datafile 'E:\app\tecpie\oradata\orcl1234\PDMS.DBF' size 400m autoextend on next 10m maxsize 5120m;

create tablespace his datafile 'D:\data\hisdata.dbf' size 200M autoextend on next 5m maxsize 500m;

创建表空间时指定表空间块大小:CREATE TABLESPACE test2 DATAFILE '+DATA' SIZE 50M BLOCKSIZE 16K;必须设置下db_16k_cache_size的大小。否则不能创建

3,改表空间名并指定数据库默认表空间

alter tablespace USERS rename to JIEK_TABLESPACE;

ALTER DATABASE DEFAULT TABLESPACE JIEK_TABLESPACE;

4,给表空间添加数据文件

alter tablespace  CS_TABLESPACE  add datafile   '/database/HEBCS/CS_TABLESPACE05.dbf' size 4G  autoextend on;

alter tablespace erpindx add datafile '+DBFILE' size 50G autoextend on maxsize 100G;

5,手动指定数据文件大小

alter database datafile 7 autoextend on maxsize 131071M

alter database datafile 7 resize 131000M

6,大文件表空间

create bigfile tablespace bigtab datafile '/u01/app/oracle/oradata/qycs/big01.dbf' size 500m autoextend on;   -------------大文件表空间,32T

alter tablespace  bigtab add datafile   '/u01/app/oracle/oradata/qycs/big02.dbf' size 500m autoextend on;   

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace   --------------一个大文件表空间只能有一个数据文件

7,删除数据文件

SQL> select status,name from v$datafile;

SQL> alter database datafile 5 offline drop;

Oracle处于recover状态的数据文件是无法使用exp导出数据的

8,数据文件更换路径

SQL> startup mount

select name from v$datafile;

select 'alter database rename file '''||name||''' to '''||replace(name,'/u01/app/oracle/product/11.2.0/db_1/dbs/','/u01/app/dbfile/')||''' ;' from v$datafile;

select 'alter database rename file '''||member||''' to '''||replace(member,'/u01/app/oracle/product/11.2.0/db_1/dbs/','/u01/app/dbfile/')||''' ;' from v$logfile;

alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/C:APPADMINISTRATORPRIPRIREDO03.LOG' to '/u01/app/oracle/huobao3/REDO03.LOG' ;

alter pluggable database rename file 'E:\ORACLE\ORADATA\SKY\5D65300BC42A495BAA7F82A5FAD39B15\DATAFILE\O1_MF_SYSTEM_GGPZ71NY_.DBF' to 'E:\o

radata\SKY\PDBSKY\DATAFILE\SYSTEM01.DBF';


五、REDO日志文件组

SQL> select  GROUP#, STATUS,TYPE,MEMBER from v$logfile;

SQL> select thread#,group#,bytes/1024/1024 from v$log;   

SQL> alter system checkpoint;

--节点1操作:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA', 'FRA') SIZE 200M;

....

---节点2操作:

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ('+DATA', 'FRA') SIZE 200M;

....

每个节点分别操作:

alter system switch logfile;  ---日志切换4次

alter system archive log current   归档当前的日志组,仅在归档模式下使用

alter system checkpoint ;  --- active 的日志变为 inactive

---删除日志组:提示需要归档的话:

SQL> alter database clear unarchived logfile group 7;

alter database drop logfile group 1;  

alter database drop logfile group 2;

alter database drop logfile group 3;

alter database drop logfile group 4;  

查看现有日志组信息

SELECT v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread# FROM v$log, v$logfile WHERE v$log.group# = v$logfile.group# group by v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024 /1024 ,v$log.thread# order by group# ;  

使归档日志强制半小时切换一次

SQL> alter system set ARCHIVE_LAG_TARGET=2700 scope=both;


六、UNDO表空间

扩大UNDO表空间
alter   database   datafile   '/opt/oracle/oradata/inms/undotbs02.dbf'   resize   4000M; 

alter tablespace UNDOTBS1 add datafile '/oracle/app/oracle/oradata/prdogg/undotbs02.dbf' size 30g autoextend on

1、创建新的UNDO表空间,并设置自动扩展参数;

create undo tablespace undotbs2 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size    2 1000m reuse autoextend on next 800m maxsize unlimited;

2、动态更改spfile配置文件;

alter system set undo_tablespace=undotbs2 scope=both;

3、删除原有的UNDO表空间;

drop tablespace undotbs1 including contents;

4、确认删除是否成功;

select name from v$tablespace;


七、TEMP表空间

1,查看临时文件的路径,大小,是否自动扩展

select * from dba_temp_files;

select * from v$tempfile;  -------查看路径,状态。

select * from dba_tablespaces;

2,暂时释放临时表空间的磁盘占用空间

新建临时表空间temp2

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/huobao3/TEMP2.DBF' SIZE 200m AUTOEXTEND ON;

设置temp2为默认临时表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

删除原temp表空间-----看(4查看temp使用率)里面的current_users是0才可以删除

drop tablespace temp including contents and datafiles;

3,给临时文件表空间添加数据文件

alter tablespace temp add tempfile '+DBFILE' size 32760m;

alter tablespace temp add tempfile '+DATA' size 30g;

手动指定数据文件大小

 alter database tempfile 文件号 resize 6000m;

 alter database tempfile '绝对路径' resize 20G;   ----------adg指定绝对路径

4查看temp使用率 ----USED_BLOCKS是使用的

SQL> select inst_id, tablespace_name,current_users, total_blocks, used_blocks, free_blocks, free_blocks/ total_blocks as pct_free from gv$sort_segment;

  INST_ID TABLESPACE CURRENT_USERS  TOTAL_BLOCKS  USED_BLOCKS   FREE_BLOCKS

----------------------------------------------------------------------------------------------------------------------------------

 1 TEMP     0     12559232    0 12559232

 2 TEMP     2        195968  384  195584

2 rows selected.


八、删除表空间

--删除空的表空间,但是不包含物理文件
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;

上一篇:数据库信息查看 下一篇:ORACLE ADG查询