oracle常用命令

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