ORACLE 数据文件创建，查询，删除，恢复 相关SQL 整理

--1、查看数据文件表结构
SELECT * FROM V$DATAFILE;
 
--2、查看分区信息表结构
SELECT * FROM dba_extents WHERE ROWNUM <10;
 
--3、查看对象物理存储空间表结构
SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'MT_TRW';
 
--4、查看表大小
SELECT TRUNC(BYTES/1024/1024)||'MB' 表大小,SEGMENT_NAME 对象名,
        PARTITION_NAME 分区名,SEGMENT_TYPE 类型 ,TABLESPACE_NAME 表空间名 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME = 'MT_TRW';
 
--5、查看表空间
 SELECT T1.NAME 表空间,T2.NAME 数据文件
FROM V$TABLESPACE T1,V$DATAFILE T2
WHERE T1.TS# = T2.TS# AND T1.NAME LIKE 'TRW%';

--6、查看数据文件大小1(建议使用第二种方法)
WITH E AS (SELECT FILE_ID,SUM(BYTES) REAL_USED_BYTES FROM DBA_EXTENTS WHERE OWNER = 'XJUMSXCPMS' GROUP BY FILE_ID)
    ,F AS( SELECT file_id ,SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY FILE_ID)
SELECT TABLESPACE_NAME 表空间名
      ,FILE_NAME	数据文件名
      ,DECODE(AUTOEXTENSIBLE, 'YES', ROUND(MAXBYTES/1073741824, 2),ROUND(USER_BYTES/1073741824, 2)) 最大扩展G  
      ,ROUND(USER_BYTES/1073741824, 2) 占硬盘G
      ,ROUND(REAL_USED_BYTES/1073741824, 2) 对象数据G
      ,ROUND(FREE_BYTES/1073741824, 2)  空闲大小G
FROM dba_data_files D JOIN E ON D.FILE_ID = E.FILE_ID
     LEFT JOIN F ON D.FILE_ID = F.FILE_ID 
ORDER BY 1,2

--7、查看数据文件大小2
WITH F AS(SELECT file_id ,SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY FILE_ID)
SELECT D.TABLESPACE_NAME 表空间名
      ,FILE_NAME	数据文件名
      ,DECODE(AUTOEXTENSIBLE, 'YES', ROUND(MAXBYTES/1073741824, 2),ROUND(USER_BYTES/1073741824, 2)) 最大扩展G 
      ,ROUND(USER_BYTES/1073741824, 2) 占硬盘G
      ,ROUND(FREE_BYTES/1073741824, 2)  空闲大小G
FROM DBA_DATA_FILES D LEFT JOIN F ON D.file_id = F.file_id 
ORDER BY 1,2
 
--8、查看表空间大小
WITH F AS(SELECT TABLESPACE_NAME,SUM(BYTES) FREE_BYTES FROM dba_free_space GROUP BY TABLESPACE_NAME)
    ,D AS(SELECT TABLESPACE_NAME
                ,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES,USER_BYTES)) MAXBYTES
                ,SUM(USER_BYTES) USER_BYTES
          FROM DBA_DATA_FILES 
          GROUP BY TABLESPACE_NAME)
SELECT D.TABLESPACE_NAME 表空间名
      ,ROUND(MAXBYTES/1073741824, 2) 最大扩展G 
      ,ROUND(USER_BYTES/1073741824, 2) 占硬盘G
      ,ROUND(FREE_BYTES/1073741824, 2)  空闲大小G
      ,ROUND((MAXBYTES-USER_BYTES+FREE_BYTES)/1073741824, 2)  可扩展空闲G
FROM D LEFT JOIN F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY 1
 
 --9、缩减数据文件大小
ALTER DATABASE DATAFILE /oracledb/data/ora11ha/TRW01 RESIZE 20M
 
--10、查看对象所在的表空间
SELECT DISTINCT OWNER 用户名,SEGMENT_NAME 对象名,PARTITION_NAME 分区名,
        SEGMENT_TYPE 类型 ,TABLESPACE_NAME 表空间名 
FROM DBA_EXTENTS 
WHERE SEGMENT_NAME = 'MT_TRW';
 
--11、查看数据文件中都有哪些对象
SELECT DISTINCT a.OWNER 用户名,a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,
       a.SEGMENT_TYPE 类型 ,a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
from DBA_EXTENTS a  JOIN V$DATAFILE b ON a.FILE_ID=b.FILE# 
WHERE b.NAME = '/oracledb/data/ora11ha/TRW01.dbf' ;
 
--12、查看表都保存在那些数据文件中
SELECT DISTINCT a.OWNER 用户名,a.SEGMENT_NAME 对象名,PARTITION_NAME 分区名,
        a.SEGMENT_TYPE 类型 ,a.TABLESPACE_NAME 表空间名 ,a.FILE_ID 文件ID ,b.NAME 文件名 
from dba_extents a  JOIN v$datafile b ON a.file_id=b.file# 
WHERE SEGMENT_NAME = 'MT_TRW';
 
--13、创建表空间
CREATE TABLESPACE TRW DATAFILE  '/oracledb/data/ora11ha/TRW01.dbf' SIZE 100 M 
       AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED；
 
--14、添加数据文件
ALTER TABLESPACE "TRW" ADD DATAFILE '/oracledb/data/ora11ha/TRW02.dbf' SIZE 100 M
      AUTOEXTEND ON NEXT 100 M;
 
--15、修改对象表空间
ALTER TABLE MT_TRW MOVE TABLESPACE TRW;
ALTER TABLE MT_TRW MOVE PARTITION MT_TRW1 TABLESPACE TRW; /*修改分区表的一个分区*/
 
--16、删除数据文件
-----在线文件删除
ALTER TABLESPACE "TRW" DROP DATAFILE '/oracledb/data/ora11ha/TRW01.dbf'; 
-----离线文件删除
--ALTER DATABASE DATAFILE '/oracledb/data/ora11ha/TRW01.dbf' OFFLINE DROP; --设为离线
ALTER DATABASE RECOVER DATAFILE '/oracledb/data/ora11ha/TRW01.dbf'; --恢复文件
ALTER DATABASE DATAFILE '/oracledb/data/ora11ha/TRW01.dbf' ONLINE;  --设为在线
ALTER TABLESPACE "TRW" DROP DATAFILE '/oracledb/data/ora11ha/TRW01.dbf'; 
 
--17、修改表指向的表空间
ALTER TABLE MT_TRW MOVE TABLESPACE TRW;
ALTER TABLE MT_TRW MOVE PARTITION MT_TRW2 TABLESPACE TRW; /*修改分区表的一个分区*/

--18、索引操作
------创建索引
CREATE INDEX MT_TRW_MSGID_INDEX ON MT_TRW(MSGID) TABLESPACE TRW  /*重建索引*/
------修改索引指向的表空间
ALTER INDEX MT_TRW_MSGID_INDEX REBUILD TABLESPACE TRW;  /*移动索引*/
------删除索引
DROP INDEX MT_TRW_MSGID_INDEX; /*删除索引*/

--19、删除创建的TRWN临时表空间及数据文件
DROP TABLESPACE TRWN INCLUDING CONTENTS AND DATAFILES;

--20、重命名数据文件
ALTER TABLESPACE "TRW" RENAME DATAFILE '/oracledb/data/ora11ha/TRW02.dbf' 
      TO '/oracledb/data/ora11ha/TRW03.dbf'
 
--21、查看数据库是否开启了回收站
SELECT NAME, VALUE FROM V$PARAMETER WHERE UPPER(NAME)='RECYCLEBIN';
 
--22、关闭回收站
ALTER SYSTEM SET RECYCLEBIN=OFF;
ALTER SESSION SET RECYCLEBIN=OFF;
 
--23、开启回收站
ALTER SYSTEM SET RECYCLEBIN=ON;
ALTER SESSION SET RECYCLEBIN =ON;
 
--24、查看回收站
SELECT * FROM RECYCLEBIN;      
SELECT * FROM DBA_RECYCLEBIN;   
SELECT * FROM USER_RECYCLEBIN;
 
--25、恢复删除表
------回收站没有同名表时(ORIGINAL_NAME)
FLASHBACK TABLE "TRW-TEST" TO BEFORE DROP; 
------回收站有同名表时(OBJECT_NAME)
FLASHBACK TABLE "BIN$N1B31IVUB6LGUBKIDGBKMG==$0" TO BEFORE DROP;
------指定时间戳恢复DELETE数据
SELECT * FROM MT_TRW AS OF TIMESTAMP TO_TIMESTAMP('2020-02-24 18:27:00', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE MT_TRW TO TIMESTAMP TO_TIMESTAMP('2020-02-24 18:27:00', 'YYYY-MM-DD HH24:MI:SS');
 ------恢复到20分钟前的数据DELETE数据
 SELECT * FROM MT_TRW AS OF TIMESTAMP SYSDATE - 20/1440;
 FLASHBACK TABLE MT_TRW TO TIMESTAMP SYSDATE - 20/1440;
 
--26、清空表并释放空间
TRUNCATE TABLE  表名 DROP STORAGE;
 
--27、令永久删除表
 DROP TABLE TABLE_NAME PURGE;
 
--28、删除回收站中数据
------删除回收站中表
PURGE TABLE "TRW-TEST";
PURGE TABLE "BIN$n1B31ivUB6LgUBKIDgBkMg==$0";
------删除回收站指定表空间的数据
PURGE TABLESPACE XJUM_SXCPMS_ORATBS;
------删除回收站全部数据
PURGE RECYCLEBIN;