Oracle表空间管理相关脚本

常规表空间

查看用户的默认表空间

set linesize 200
SELECT username,default_tablespace FROM dba_users WHERE account_status='OPEN' ORDER BY 1;

查看表空间的数据文件及是否打开自动扩展

col file_name for a80
col tablespace_name for a20
col autoextensible for a20
 
SELECT tablespace_name,
       file_id,
       file_name,
       round(bytes/(1024*1024*1024), 0) total_gb,
       autoextensible
  FROM dba_data_files
 ORDER BY 2;

查看某张表属于哪个表空间

SELECT owner, table_name, tablespace_name
  FROM dba_tables
 WHERE table_name = 'table_name';

给表空间增加数据文件

ALTER TABLESPACE users ADD DATAFILE '+RACDATA' SIZE 1G AUTOEXTEND ON NEXT 100M;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;

查看表空间大小及使用情况

SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
       FREE_SPACE "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
--如果采用了完全本地管理的临时表空间，就加入如下部分
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE "USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
       NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

设置表空间只读

1) 设置表空间只读
SQL> ALTER TABLESPACE USERS READ ONLY;
2) 设置表空间再次可写
SQL> ALTER TABLESPACE USERS READ WRITE;

设置默认表空间

ALTER DATABASE DEFAULT TABLESPACE users;

查看表空间定义语句

set pages 0
set long 1000000
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'XXX_TS') FROM DBA_TABLESPACES TS;

重命名数据文件

1) 设置表空间脱机
   SQL> ALTER TABLESPACE USERS OFFLINE NORMAL;
2) 用一个操作系统实用程序重新命名此文件如UNIX的cp或mv，或者windows的copy命令
   $cp /u01/app/oracle/test01.dbf /u02/app/oracle/test01.dbf
3) 在数据文件在线之前使用以下命令重命名数据库中的数据文件
   SQL> ALTER TABLESPACE USERS RENAME DATAFILE '/u01/app/oracle/oradata/TSH1/users01.dbf' TO '/u02/app/oracle/oradata/TSH1/users01.dbf'
4)设置表空间online
   SQL> ALTER TABLESPACE USERS ONLINE;

临时表空间

查看临时表空间

select tablespace_name,file_name,bytes/1024/1024 file_size_mb,autoextensible from dba_temp_files;
select status, enabled, name,bytes/1024/1024 file_size_mb from v_$tempfile;

查看临时表空间使用率

SELECT TMP_TBS.TABLESPACE_NAME,
       SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
       SUM(USED_TOT.USED_MB) USED_MB,
       SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) TMP_TBS,
       (SELECT TMP_USED.TABLESPACE,
               SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
          FROM V$SORT_USAGE TMP_USED,
               (SELECT VALUE DB_BLOCK_SIZE
                  FROM V$PARAMETER
                 WHERE NAME = 'db_block_size') PARA
         GROUP BY TMP_USED.TABLESPACE) USED_TOT
 where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
 GROUP BY TMP_TBS.TABLESPACE_NAME;

查看临时表空间当前使用的大小和历史最大使用的大小

select 'max' asstatus,
       tablespace_name,
       sum(bytes_cached)/1024/1024 as usage_mb
  from v$temp_extent_pool
 group by tablespace_name
union all
select 'current' as status,
       ss.tablespace_name,
       sum((ss.used_blocks * ts.blocksize))/1024/1024 as usage_mb
  from gv$sort_segment ss, sys.ts$ ts
 where ss.tablespace_name = ts.name
 group by ss.tablespace_name;

更改系统的默认临时表空间：

--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间：
select username,temporary_tablespace from dba_users;
--更改某一用户的临时表空间：
alter user scott temporary tablespace temp;