-- 查询当前会话id(sid),会话序列号(serial#),操作系统进程id(spid)SELECT a.SID,
b.SERIAL#,
c.SPID
FROM v$mystat a,
v$session b,
v$process c
WHERE a.SID = b.SID
and b.PADDR=c.ADDR
AND ROWNUM =1;
-- 查询字符集SELECTvalue$ characterset FROM sys.props$ WHERE name='NLS_CHARACTERSET';SELECT userenv('language') characterset FROM dual;
查看回收站情况
-- 查看回收站情况SELECT'状态:'|| a.VALUE||',占用空间:'||(SELECTround(SUM(a.space *(SELECTvalueFROM v$parameter
WHERE name ='db_block_size'))/1024/1024,2)||'M,共'||count(1)||'个对象'FROM cdb_recyclebin a) recyclebin1
FROM v$parameter a
WHERE a.NAME ='recyclebin';SELECT*FROM RECYCLEBIN;SELECT*FROM DBA_RECYCLEBIN;SELECT*FROM USER_RECYCLEBIN;SELECT*FROM CDB_RECYCLEBIN;
数据库系统PSU信息
-- 数据库系统PSU信息SELECT d.con_id,
to_char(d.action_time,'YYYY-MM-DD HH24:MI:SS') action_time,
d.action,
d.namespace,
d.id,--d.bundle_series,
d.comments
FROM CDB_REGISTRY_HISTORY d
ORDERBY d.con_id, d.action_time;
数据库大小
-- 数据库大小-- ts_datafile_physical_size_G表示所有表空间的物理文件实际占用大小,即表空间大小(不包括temp表空间)-- ts_tempfile_physical_size_G表示所有临时表空间的文件实际占用大小-- ts_datafile_used_size_G表示所有表空间的使用大小,数据文件实际使用大小,RMAN非压缩备份大小(若使用压缩as compressed备份则至少可以减少一半空间)select A.CON_ID,
A.ts_datafile_physical_size_G,
B.ts_tempfile_physical_size_G,
C.ts_datafile_used_size_G
FROM(select A.CON_ID,round(sum(bytes)/1024/1024/1024,2) ts_datafile_physical_size_G
from CDB_data_files A
GROUPBY A.CON_ID) A,(select A.CON_ID,round(sum(bytes)/1024/1024/1024,2) ts_tempfile_physical_size_G
from CDB_temp_files A
GROUPBY A.CON_ID) B,(select A.CON_ID,round(sum(bytes)/1024/1024/1024,2) ts_datafile_used_size_G
from CDB_segments A
GROUPBY A.CON_ID) C
WHERE A.CON_ID = B.CON_ID
AND A.CON_ID = C.CON_ID
ORDERBY con_id;
查看表空间状况
常规库表空间情况查询,非CDB
WITH wt1 AS(SELECT ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,'D',
nvl(fs.FREESIZ,0),'T',
df.all_bytes - nvl(fs.FREESIZ,0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
ts.bigfile,
df.ts_df_count
FROM dba_tablespaces ts,(SELECT'D'TYPE,
TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES,0, BYTES, MAXBYTES)) MAXSIZ
FROM dba_data_files d
GROUPBY TABLESPACE_NAME
UNIONALLSELECT'T',
TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES,0, BYTES, MAXBYTES))FROM dba_temp_files d
GROUPBY TABLESPACE_NAME) df,(SELECT TABLESPACE_NAME,SUM(BYTES) FREESIZ
FROM dba_free_space
GROUPBY TABLESPACE_NAME
UNIONALLSELECT tablespace_name,SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a, dba_tablespaces d
WHERE a.tablespace= d.tablespace_name
GROUPBY tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))SELECT(SELECT A.TS#FROM V$TABLESPACE A
WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
t.TABLESPACE_NAME TS_Name,round(t.all_bytes /1024/1024) ts_size_M,round(t.freesiz /1024/1024) Free_Size_M,round((t.all_bytes - t.FREESIZ)/1024/1024) Used_Size_M,round((t.all_bytes - t.FREESIZ)*100/ t.all_bytes,3) Used_per,round(MAXSIZ /1024/1024/1024,3) MAX_Size_g,round(decode(MAXSIZ,0, to_number(NULL),(t.all_bytes - FREESIZ))*100/
MAXSIZ,1) USED_per_MAX,round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.FORCE_LOGGING,
t.CONTENTS,
t.EXTENT_MANAGEMENT,
t.SEGMENT_SPACE_MANAGEMENT,
t.RETENTION,
t.DEF_TAB_COMPRESSION,
t.bigfile,
t.ts_df_count
FROM wt1 t;
CBD表空间情况查询
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,type,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM(WITH wt1 AS(SELECT ts.CON_ID,(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,(SELECT A.TS#FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)AND a.CON_ID = tS.con_id) TS#,
ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,'D',
nvl(fs.FREESIZ,0),'T',
df.all_bytes - nvl(fs.FREESIZ,0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM cdb_tablespaces ts,(SELECT d.CON_ID,'D'TYPE,
TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES,0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUPBY d.CON_ID,
TABLESPACE_NAME
UNIONALLSELECT d.CON_ID,'T',
TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES,0, BYTES, MAXBYTES))FROM cdb_temp_files d
GROUPBY d.CON_ID,
TABLESPACE_NAME) df,(SELECT d.CON_ID,
TABLESPACE_NAME,SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUPBY d.CON_ID,
TABLESPACE_NAME
UNIONALLSELECT d.CON_ID,
tablespace_name,SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace= d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUPBY d.CON_ID,
tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)AND ts.CON_ID = fs.CON_ID(+))SELECT T.CON_ID,(CASEWHEN T.PDBNAME = LAG(T.PDBNAME,1)OVER(PARTITIONBY T.PDBNAME ORDERBY TS#) THENNULLELSE
T.PDBNAME
END) PDBNAME,
TS#,
t.TABLESPACE_NAME TS_Name,
t.CONTENTS type,round(t.all_bytes /1024/1024) ts_size_M,round(t.freesiz /1024/1024) Free_Size_M,round((t.all_bytes - t.FREESIZ)/1024/1024) Used_Size_M,round((t.all_bytes - t.FREESIZ)*100/ t.all_bytes,3) Used_per,round(MAXSIZ /1024/1024/1024,3) MAX_Size_g,round(decode(MAXSIZ,0,
to_number(NULL),(t.all_bytes - FREESIZ))*100/ MAXSIZ,3) USED_per_MAX,round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t);