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