上海高端品牌网站制作,可以用服务器做网站,大型行业门户网站开发建设方案,网站系统里不能打印oracle常用sql oracle常用sql查询当前会话id（sid），会话序列号（serial#），操作系统进程id（spid）查询数据库信息查询实例信息查询字符集查看回收站情况数据库系统PSU信息数据库大小查看表空间状况常规库表空间情况查询，非CDBCBD表空间情况查询当前客户端信息资源使用情况… oracle常用sql oracle常用sql查询当前会话id（sid），会话序列号（serial#），操作系统进程id（spid）查询数据库信息查询实例信息查询字符集查看回收站情况数据库系统PSU信息数据库大小查看表空间状况常规库表空间情况查询，非CDBCBD表空间情况查询 当前客户端信息资源使用情况查看所有参数数据库闪回空间使用情况数据库闪回空间总体使用情况数据库闪回空间详细使用情况 临时表空间使用情况数据文件状况控制文件ASM磁盘使用情况ASM磁盘组使用情况ASM磁盘组参数配置情况闪回参数查询归档参数查询归档路径查询近一个月归档日志情况近一月内产生的归档日志总大小近一月内每天产生的归档日志总大小日志组大小SGA使用情况SGA配置信息SGA target advicePGA Target advice文件IO信息文件IO读写分析文件IO时间分析 逻辑读TOP10的SQL语句物理读TOP10的SQL语句执行时间TOP10的SQL语句执行次数TOP10的SQL语句解析次数TOP10的SQL语句VERSION_COUNT TOP10的SQL语句内存TOP10的SQL语句DISK_SORT严重的SQL从ASH视图查询SQL最耗CPU的SQL语句最耗I/O的SQL语句最消耗资源的SQL语句 执行时间最长SQL数据库用户查询拥有DBA角色的用户拥有SYS角色的用户超过1000行无主键的表只取数据量最大的前50张表，行数大于1000查找大小超过10GB的表历史ACTIVE会话数(按照小时展示)常规库查看LOCK锁情况常规库查看谁锁住了谁CDB库查看锁情况 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
