oracle常用sql

oracle常用sql

查询当前会话id（sid），会话序列号（serial#），操作系统进程id（spid）

-- 查询当前会话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;

查询数据库信息

-- 查询数据库信息
SELECTDBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON,DB_UNIQUE_NAME
FROM  V$DATABASE;

查询实例信息

-- 查询实例信息
SELECTINSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUS,THREAD#
FROM V$INSTANCE;

查询字符集

-- 查询字符集
SELECT value$ characterset FROM sys.props$ WHERE name='NLS_CHARACTERSET';
SELECT userenv('language') characterset FROM dual;

查看回收站情况

-- 查看回收站情况
SELECT '状态：' || a.VALUE || '，占用空间：' ||(SELECT round(SUM(a.space * (SELECT valueFROM v$parameterWHERE name = 'db_block_size')) / 1024 / 1024,2) || 'M，共' || count(1) || '个对象'FROM cdb_recyclebin a) recyclebin1FROM v$parameter aWHERE 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.commentsFROM CDB_REGISTRY_HISTORY dORDER BY 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_GFROM (select A.CON_ID,round(sum(bytes) / 1024 / 1024 / 1024, 2) ts_datafile_physical_size_Gfrom CDB_data_files AGROUP BY A.CON_ID) A,(select A.CON_ID,round(sum(bytes) / 1024 / 1024 / 1024, 2) ts_tempfile_physical_size_Gfrom CDB_temp_files AGROUP BY A.CON_ID) B,(select A.CON_ID,round(sum(bytes) / 1024 / 1024 / 1024, 2) ts_datafile_used_size_Gfrom CDB_segments AGROUP BY A.CON_ID) CWHERE A.CON_ID = B.CON_IDAND A.CON_ID = C.CON_IDORDER BY 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_countFROM dba_tablespaces ts,(SELECT 'D' TYPE,TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZFROM dba_data_files dGROUP BY TABLESPACE_NAMEUNION ALLSELECT 'T',TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))FROM dba_temp_files dGROUP BY TABLESPACE_NAME) df,(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZFROM dba_free_spaceGROUP BY TABLESPACE_NAMEUNION ALLSELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytesFROM gv$sort_usage a, dba_tablespaces dWHERE a.tablespace = d.tablespace_nameGROUP BY tablespace_name) fsWHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAMEAND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#FROM V$TABLESPACE AWHERE 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_countFROM 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.NAMEFROM   V$CONTAINERS npWHERE  np.CON_ID = tS.con_id) PDBNAME,(SELECT A.TS#FROM   V$TABLESPACE AWHERE  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_countFROM   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)) MAXSIZFROM   cdb_data_files dGROUP  BY d.CON_ID,TABLESPACE_NAMEUNION ALLSELECT d.CON_ID,'T',TABLESPACE_NAME,COUNT(*) ts_df_count,SUM(BYTES) all_bytes,SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))FROM   cdb_temp_files dGROUP  BY d.CON_ID,TABLESPACE_NAME) df,(SELECT d.CON_ID,TABLESPACE_NAME,SUM(BYTES) FREESIZFROM   cdb_free_space dGROUP  BY d.CON_ID,TABLESPACE_NAMEUNION ALLSELECT d.CON_ID,tablespace_name,SUM(d.BLOCK_SIZE * a.BLOCKS) bytesFROM   gv$sort_usage   a,cdb_tablespaces dWHERE  a.tablespace = d.tablespace_nameAND    a.CON_ID = d.CON_IDGROUP  BY d.CON_ID,tablespace_name) fsWHERE  ts.TABLESPACE_NAME = df.TABLESPACE_NAMEAND    ts.CON_ID = df.CON_IDAND    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(PARTITION BY T.PDBNAME ORDER BY TS#) THENNULLELSET.PDBNAMEEND) 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_countFROM  wt1 t);

当前客户端信息

SELECT sys_context('USERENV', 'ACTION') ACTION,sys_context('USERENV', 'AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,sys_context('USERENV', 'AUTHENTICATION_TYPE') AUTHENTICATION_TYPE,sys_context('USERENV', 'AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,sys_context('USERENV', 'CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA,sys_context('USERENV', 'CURRENT_USER') CURRENT_USER,sys_context('USERENV', 'DATABASE_ROLE') DATABASE_ROLE,sys_context('USERENV', 'DB_NAME') DB_NAME,sys_context('USERENV', 'DB_UNIQUE_NAME') DB_UNIQUE_NAME,sys_context('USERENV', 'HOST') HOST,sys_context('USERENV', 'IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,sys_context('USERENV', 'INSTANCE') INSTANCE,sys_context('USERENV', 'INSTANCE_NAME') INSTANCE_NAME,sys_context('USERENV', 'IP_ADDRESS') IP_ADDRESS,sys_context('USERENV', 'ISDBA') ISDBA,sys_context('USERENV', 'LANG') LANG,sys_context('USERENV', 'LANGUAGE') LANGUAGE,sys_context('USERENV', 'MODULE') MODULE,sys_context('USERENV', 'NETWORK_PROTOCOL') NETWORK_PROTOCOL,sys_context('USERENV', 'NLS_CALENDAR') NLS_CALENDAR,sys_context('USERENV', 'NLS_CURRENCY') NLS_CURRENCY,sys_context('USERENV', 'NLS_DATE_FORMAT') NLS_DATE_FORMAT,sys_context('USERENV', 'NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,sys_context('USERENV', 'NLS_SORT') NLS_SORT,sys_context('USERENV', 'NLS_TERRITORY') NLS_TERRITORY,sys_context('USERENV', 'OS_USER') OS_USER,sys_context('USERENV', 'SERVER_HOST') SERVER_HOST,sys_context('USERENV', 'SERVICE_NAME') SERVICE_NAME,sys_context('USERENV', 'SESSION_EDITION_ID') SESSION_EDITION_ID,sys_context('USERENV', 'SESSION_EDITION_NAME') SESSION_EDITION_NAME,sys_context('USERENV', 'SESSION_USER') SESSION_USER,sys_context('USERENV', 'SESSIONID') SESSIONID,sys_context('USERENV', 'SID') SID,sys_context('USERENV', 'TERMINAL') TERMINALFROM dual;

资源使用情况

SELECT a.con_id,a.inst_id as "实例id",a.resource_name as "资源名称",a.current_utilization as "当前值",a.max_utilization as "最大值",a.initial_allocation as "初始值",a.limit_value as "限制值"FROM gv$resource_limit aorder by a.con_id, a.inst_id, a