• oracle常用sql

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    查询数据库信息

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    查询实例信息

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    查询字符集

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

    • 1
    • 2
    • 3

    查看回收站情况

    -- 查看回收站情况
SELECT '状态：' || a.VALUE || '，占用空间：' ||
       (SELECT round(SUM(a.space * (SELECT value
                                      FROM 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    数据库系统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
 ORDER BY d.con_id, d.action_time;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    数据库大小

    -- 数据库大小
-- 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
         GROUP BY 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
         GROUP BY A.CON_ID) B,
       (select A.CON_ID,
               round(sum(bytes) / 1024 / 1024 / 1024, 2) ts_datafile_used_size_G
          from CDB_segments A
         GROUP BY A.CON_ID) C
 WHERE A.CON_ID = B.CON_ID
   AND A.CON_ID = C.CON_ID
   ORDER BY con_id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    查看表空间状况

    常规库表空间情况查询，非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
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT 'T',
                 TABLESPACE_NAME,
                 COUNT(*) ts_df_count,
                 SUM(BYTES) all_bytes,
                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
            FROM dba_temp_files d
           GROUP BY TABLESPACE_NAME) df,
         (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
            FROM dba_free_space
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
            FROM gv$sort_usage a, dba_tablespaces d
           WHERE a.tablespace = d.tablespace_name
           GROUP BY 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    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
                             GROUP  BY d.CON_ID,
                                       TABLESPACE_NAME
                             UNION ALL
                             SELECT 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
                             GROUP  BY d.CON_ID,
                                       TABLESPACE_NAME) df,
                            (SELECT d.CON_ID,
                                    TABLESPACE_NAME,
                                    SUM(BYTES) FREESIZ
                             FROM   cdb_free_space d
                             GROUP  BY d.CON_ID,
                                       TABLESPACE_NAME
                             UNION ALL
                             SELECT 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
                             GROUP  BY 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,
                  (CASE
                      WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
                       OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
                       NULL
                      ELSE
                       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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104

    当前客户端信息

    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') TERMINAL
  FROM dual;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    资源使用情况

    SELECT a.con_id,
       a.inst_id as "实例id",
       a.resource_name as "资源名称",
       a.current_utilization as "当前值",
       a.max_utilization as "最大值"
    • 1
    • 2
    • 3
    • 4
  • 相关阅读:
    按位运算符、逻辑运算符
    基于智能优化算法的交通流模拟器（Matlab代码实现）
    简单易懂的时序数据压缩算法分析
    网络安全（黑客）自学
    6个机器学习可解释性框架
    设计模式-创建型模式
    【Computer Composition】Part1 计算机系统概述
    阿里云的“终端云化”实践，基于ENS进行边缘架构构建
    驱动有没有（静态和动态加载）
    NNDL 实验七 循环神经网络（3）LSTM的记忆能力实验
  • 原文地址：https://blog.csdn.net/u011143903/article/details/128215110