代码改变世界

Oracle表空间管理相关脚本

  abce  阅读(173)  评论(0编辑  收藏  举报

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;

  

 

编辑推荐:
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· .NET 进程 stackoverflow异常后,还可以接收 TCP 连接请求吗?
阅读排行:
· 本地部署 DeepSeek:小白也能轻松搞定!
· 基于DeepSeek R1 满血版大模型的个人知识库,回答都源自对你专属文件的深度学习。
· 在缓慢中沉淀,在挑战中重生!2024个人总结!
· 大人,时代变了! 赶快把自有业务的本地AI“模型”训练起来!
· Tinyfox 简易教程-1:Hello World!
点击右上角即可分享
微信分享提示