Skip to main contentdfsdf

Vinay R's List: oracle metadata query

  • Aug 06, 14

    oracle role privilege query

    SELECT d.grantee, r.role, r.privilege

    FROM DBA_role_privs d, role_sys_privs r

    WHERE d.grantee not like ('A%') and d.granted_role = r.role

    ORDER BY d.grantee, r.role, r.privilege asc;

  • Aug 07, 14

    oracle metadata query constraint primary foreign check

    SELECT alc.constraint_name,

    CASE alc.constraint_type

    WHEN 'P' THEN 'PRIMARY KEY'

    WHEN 'R' THEN 'FOREIGN KEY'

    WHEN 'U' THEN 'UNIQUE'

    WHEN 'C' THEN 'CHECK'

    END "constraint_type",

    alc.DELETE_RULE "on_delete",

    CASE alc.deferrable WHEN 'NOT DEFERRABLE' THEN 0 ELSE 1 END "deferrable",

    CASE alc.deferred WHEN 'IMMEDIATE' THEN 1 ELSE 0 END "initially_deferred",

    alc.search_condition,alc.table_name, cols.column_name, cols.position,

    r_alc.table_name "references_table", r_cols.column_name "references_field", r_cols.position "references_field_position"

    FROM all_cons_columns cols LEFT JOIN all_constraints alc ON alc.constraint_name = cols.constraint_name

    AND alc.owner = cols.owner

    LEFT JOIN all_constraints r_alc ON alc.r_constraint_name = r_alc.constraint_name AND alc.r_owner = r_alc.owner

    LEFT JOIN all_cons_columns r_cols ON r_alc.constraint_name = r_cols.constraint_name AND r_alc.owner = r_cols.owner AND cols.position = r_cols.position

    WHERE alc.constraint_name = cols.constraint_name AND alc.table_name = '?';

  • Sep 09, 14

    SELECT a.file_name,
           substr(A.tablespace_name,1,14) tablespace_name,
           trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
           trunc(a.bytes/1024/1024) allocated_mb,
           trunc(A.MAXSIZE/1024/1024) capacity,
           a.autoextensible ae
    FROM (
         SELECT file_id, file_name,
                tablespace_name,
                autoextensible,
                bytes,
                decode(autoextensible,'YES',maxbytes,bytes) maxsize
         FROM   dba_data_files
         GROUP BY file_id, file_name,
                  tablespace_name,
                  autoextensible,
                  bytes,
                  decode(autoextensible,'YES',maxbytes,bytes)
         ) a,
         (SELECT file_id,
                 tablespace_name,
                 sum(bytes) free
          FROM   dba_free_space
          GROUP BY file_id,
                   tablespace_name
          ) b
    WHERE a.file_id=b.file_id(+)
    AND A.tablespace_name=b.tablespace_name(+)
    ORDER BY A.tablespace_name ASC; 

  • Sep 30, 14

    oracle lock query metadata

    SELECT l.session_id||','||v.serial# sid_serial,
           l.ORACLE_USERNAME ora_user,
           o.object_name, 
           o.object_type, 
           DECODE(l.locked_mode,
              0, 'None',
              1, 'Null',
              2, 'Row-S (SS)',
              3, 'Row-X (SX)',
              4, 'Share',
              5, 'S/Row-X (SSX)',
              6, 'Exclusive', 
              TO_CHAR(l.locked_mode)
           ) lock_mode,
           o.status, 
           to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
    FROM dba_objects o, gv$locked_object l, v$session v
    WHERE o.object_id = l.object_id
          and l.SESSION_ID=v.sid
    order by 2,3;

  • Sep 30, 14

    oracle metadata query running-queries sid

    SELECT t.*
      FROM v$open_cursor c
      JOIN v$sqltext t ON c.hash_value = t.hash_value
                     AND c.address = t.address
    WHERE sid = <sid>
    ORDER BY t.hash_value, t.sql_id, t.piece

  • Oct 14, 14

    oracle metadata query userrole

    SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
    FROM (
    SELECT NULL grantee, username granted_role
    FROM dba_users
    WHERE username LIKE UPPER(?)
    UNION
    SELECT grantee, granted_role
    FROM dba_role_privs
    UNION
    SELECT grantee, privilege
    FROM dba_sys_privs)
    START WITH grantee IS NULL
    CONNECT BY grantee = prior granted_role

  • Oct 16, 14

    oracle metadata query opencursor

    SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur, a.sid FROM v$sesstat a, v$statname b, v$parameter p

    WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors'

    group by a.sid, p.value

    order by max(a.value) desc

  • Oct 22, 14

    oracle session wait metadata query

    select event, state, count(*) from v$session_wait group by event, state order by 3 desc

  • Nov 10, 14

    oracle metadata query howto

    SELECT * FROM ALL_MVIEW_ANALYSIS 
    where 
    owner = ? 
    order by last_refresh_date desc;
1 - 11 of 11
20 items/page
List Comments (0)