Oracle表空间管理相关脚本

Oracle表空间管理相关脚本

常规表空间

查看用户的默认表空间

?

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

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

?

1 2 3 4 5 6 7 8 9 10 11

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;

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

?

1 2 3

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

给表空间增加数据文件

?

1 2 3

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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 2 3 4

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

设置默认表空间

?

1	ALTER DATABASE DEFAULT TABLESPACE users;

查看表空间定义语句

?

1 2 3

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

重命名数据文件

?

1 2 3 4 5 6 7 8

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;

临时表空间

查看临时表空间

?

1 2	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;

查看临时表空间使用率

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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;

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

?

1 2 3 4 5 6 7 8 9 10 11 12

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;

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

?

1 2 3 4 5 6 7 8

--查询默认临时表空间 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;