一 Oracle数据库查看表空间sql语句
1、oracle查看表空间当前用户
SQL> select username,default_tablespace from user_users;
2、oracle 查看表所属表空间
SQL> SELECT
TABLE_NAME,TABLESPACE_NAME
FROM USER_TABLES
where TABLE_NAME = 'test_table'
3、oracle查看表空间大小(单位不是GB)
SQL> SELECT
a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
( total - free ) "表空间使用大小",
Round(( total - free ) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE
a.tablespace_name = b.tablespace_name
4、oracle查看表空间大小 -单位GB
SQL> SELECT
a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE
a. tablespace_name = b.tablespace_name;
5.--Oracle查看表所属表空间
SQL> SELECT TABLE_NAME,TABLESPACE_NAME
FROM USER_TABLES
WHERE TABLE_NAME='test_table';
二 查看Oracle数据库表空间剩余
1)查看表空间物理文件的名称及大小
SQL> SELECT tablespace_name,
file_id,
file_name,
round(bytes/(1024*1024*1024), 2)||' GB' total_space
FROM dba_data_files
ORDER BY tablespace_name;
2)查看表空间的使用情况
SELECT round(SUM(bytes)/(1024*1024*1024),2)||' GB' AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
3)查看表空间的使用情况
SELECT a.tablespace_name,
round(a.bytes/(1024*1024*1024),2)||' GB' total,
round(b.bytes/(1024*1024*1024),2)||' GB' used,
round(c.bytes/(1024*1024*1024),2)||' GB' free,
round((b.bytes * 100) / a.bytes)||'%' "% USED ",
round((c.bytes * 100) / a.bytes)||'%' "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
三 查看、修改Oracle数据库表空间大小
查看所有表空间信息
select tablespace_name,file_id,bytes/1024/1024,file_name
from dba_data_files order by file_id;
-- 修改表空间大小
ALTER TABLESPACE WEBSITE ADD DATAFILE
'D:\SDE06.DBF' SIZE 20480M; -- 此处文件路径可以由上面查到的路径而来
四 ORACLE 查看RMAN的备份信息总结
1,检查某个时间段备份情况
SELECT t.START_TIME,t.END_TIME,t.STATUS,t.OBJECT_TYPE,t.RECIDFROM V$RMAN_STATUS t where t.START_TIME between trunc(sysdate)-2 and sysdateand t.OPERATION='BACKUP';
SQL> SELECT ROW_TYPE,COMMAND_ID,OPERATION,STATUS,OBJECT_TYPE FROM V$RMAN_STATUS WHERE START_TIME >= trunc(sysdate);
2,从V$RMAN_BACKUP_JOB_DETAILS中查看RMAN备份更详细的信息
SQL> set sqlformat ansiconsole
SQL> SELECT START_TIME,END_TIME,OUTPUT_DEVICE_TYPE,STATUS,ELAPSED_SECONDS,COMPRESSION_RATIO,INPUT_BYTES_DISPLAY,OUTPUT_BYTES_DISPLAYFROM V$RMAN_BACKUP_JOB_DETAILS where START_TIME>=trunc(sysdate)-1 ORDER BY START_TIME DESC;
SQL> SELECT MAND_ID as "备份名",t.STATUS as "狀態",t.START_TIME as "開始時間",t.TIME_TAKEN_DISPLAY as "所用時間",t.END_TIME as "結束時間",t.INPUT_TYPE as "類型",t.OUTPUT_DEVICE_TYPE as "輸出設備",t.INPUT_BYTES_DISPLAY as "輸入大小",t.OUTPUT_BYTES_DISPLAY as "輸出大小",t.OUTPUT_BYTES_PER_SEC_DISPLAY as "輸出速率(每秒)"FROM V$RMAN_BACKUP_JOB_DETAILS t where START_TIME>=trunc(sysdate)-7 ORDER BY START_TIME DESC;
3,查看所有备份集详细信息
SQL> SELECT A.RECID "BACKUP SET",A.SET_STAMP,DECODE (B.INCREMENTAL_LEVEL,'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),1, 'Incr-1级',0, 'Incr-0级',B.INCREMENTAL_LEVEL)"Type LV",B.CONTROLFILE_INCLUDED "包含CTL",DECODE (A.STATUS,'A', 'AVAILABLE','D', 'DELETED','X', 'EXPIRED','ERROR')"STATUS",A.DEVICE_TYPE "Device Type",A.START_TIME "Start Time",PLETION_TIME "Completion Time",A.ELAPSED_SECONDS "Elapsed Seconds",A.BYTES/1024/1024/1024 "Size(G)",PRESSED,A.TAG "Tag",A.HANDLE "Path"FROM GV$BACKUP_PIECE A, GV$BACKUP_SET BWHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'ORDER BY PLETION_TIME DESC;
4,查找某个备份集中包含数据文件
SELECT DISTINCT c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE DWHERE A.SET_STAMP = C.SET_STAMP AND D.FILE# = C.FILE# AND A.DELETED='NO' and a.START_TIME>trunc(sysdate) AND c.set_stamp='999651695' ORDER BY C.FILE#;
5,查询某个备份集中控制文件
SELECT DISTINCT A.SET_STAMP,D.NAME,C.CHECKPOINT_CHANGE#,C.CHECKPOINT_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE DWHERE A.SET_STAMP = C.SET_STAMP AND C.FILE# = 0 AND A.DELETED = 'NO'and a."START_TIME">trunc(sysdate) AND C.SET_STAMP = '999651780';
6,查看某个备份集中归档日志
SELECT DISTINCT B.SET_STAMP,B.THREAD#,B.SEQUENCE#,B.FIRST_TIME,B.FIRST_CHANGE#,B.NEXT_TIME,B.NEXT_CHANGE#FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE AWHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'and a.START_TIME>trunc(sysdate) AND B.SET_STAMP ='999651783'ORDER BY THREAD#, SEQUENCE#;
7,查看某个备份集SPFILE
SELECT DISTINCT A.SET_STAMP, PLETION_TIME,HANDLEFROM V$BACKUP_SPFILE B, V$BACKUP_PIECE AWHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'and a."START_TIME">trunc(sysdate) and B.SET_STAMP ='999651798';
8,查询RMAN的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
如果觉得《Oracle数据库查看表空间sql语句 查看Oracle数据库表空间剩余 修改表空间 库备份》对你有帮助,请点赞、收藏,并留下你的观点哦!