表空间相关数据库常用脚本

创建表空间

CREATE TABLESPACE "TABLESPACE_NAME" 
    LOGGING 
    DATAFILE '/data/oracle/oradata/DMSTEST/hdm_data01.dbf' SIZE 100M 
    AUTOEXTEND 
    ON NEXT  100M MAXSIZE  8000M EXTENT MANAGEMENT LOCAL SEGMENT 
SPACE MANAGEMENT  AUTO;

表空间相关属性修改

扩展表空间数据文件大小

alter database datafile '/data/oracle/oradata/DMSTEST/hdm_data01.dbf' resize 9000M;

增加表空间数据文件个数

ALTER TABLESPACE "HDM_DATA"  add datafile '/data/oracle/oradata/DMSTEST/hdm_data02.dbf' SIZE 100M  AUTOEXTEND ON NEXT 100M MAXSIZE  8000M;

设置表空间自动扩展属性

alter database datafile '/data/oracle/oradata/DMSTEST/hdm_data01.dbf' autoextend on next 100M maxsize 8000M;

表空间数据文件查询

SELECT file_name                      "数据文件位置及名称"
       ,file_id                       "文件ID" /*整个数据库中每个文件的ID都是唯一的*/
       ,tablespace_name               "表空间名称"
       ,bytes / 1024.0 / 1024.0       "表空间大小(MB)" /*换算成MB BYTES/1025/1024*/
       ,blocks                        "文件的块数量" /*BLOCKS=BYTES/1024/BLOCK_SIZE*/
       ,status                        "状态" /*标识文件当前是否可用*/
       ,relative_fno                  "相对文件号" /*相对文件号只在表空间唯一*/
       ,autoextensible                "是否自动扩展"
       ,maxbytes / 1024.0 / 1024.0    "最大可扩展空间(MB)"
       ,maxblocks                     "最大可扩展数据块"
       ,increment_by                  "每次增加的块数量"
       ,user_bytes / 1024.0 / 1024.0  "实际有用字节(MB)"
       ,user_blocks                   "实际有用的块"
       ,online_status                 "在线状态"
  FROM dba_data_files t;

空闲表空间查询

SELECT tablespace_name  "表空间名称"
       ,file_id         "文件ID" /*整个数据库中每个文件的ID都是唯一的*/
       --,block_id        "块ID"
       ,sum(bytes)/1024.0/1024.0          "占用空间大小(MB)" /*换算成MB BYTES/1025/1024*/
       --,blocks          "文件的块数量" /*BLOCKS=BYTES/1024/BLOCK_SIZE*/
       --,relative_fno    "相对文件号" /*相对文件号只在表空间唯一*/
  FROM dba_free_space t
  GROUP BY t.TABLESPACE_NAME,t.FILE_ID;

表空间使用情况查询

SELECT a.tablespace_name                                       "表空间名"
       ,a.bytes / 1024 / 1024                                  "表空间大小(M)"
       ,(a.bytes - b.bytes) / 1024 / 1024                      "已使用空间(M)"
       ,b.bytes/1024/1024                                      "空闲空间(M)"
       ,a.maxbytes/1024/1024                                   "最大可扩展空间(MB)"
       ,a.autoextensible                                       "是否自动扩展"
       ,round(((a.bytes - b.bytes) / a.bytes) * 100, 2)||'%'   "使用比"
  FROM (SELECT tablespace_name, SUM(bytes) bytes,SUM(maxbytes) maxbytes,max(autoextensible) autoextensible
          FROM dba_data_files
         GROUP BY tablespace_name) a
      ,(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
          FROM dba_free_space
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;

表空间占用详细对象情况查询

/* 表空间占用详细对象情况查询*/
SELECT a.table_name
      ,a.column_name
      ,b.segment_name
      ,b.segment_type
      ,b.tablespace_name
      ,b.bytes / 1024 / 1024
      ,b.blocks
      ,b.extents
  FROM dba_lobs a, dba_segments b
 WHERE a.segment_name = b.segment_name
 ORDER BY b.bytes DESC;