This is really useful to remove “noise” on queries:

AND NOT owner IN (
       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
    )

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

SELECT current_scn FROM v$database;

SELECT timestamp_to_scn(to_timestamp('24/09/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) AS scn FROM dual; 

SELECT scn_to_timestamp(7705798324) AS TIMESTAMP FROM dual; 

SET LINES 110
col strtd hea 'STARTED'
col instance_name FOR a8 hea 'INSTANCE'
col host_name FOR a15 hea 'HOSTNAME'
col version FOR a10
 
SELECT instance_name, version, host_name, STATUS, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
FROM v$instance;

col instnace format a35
SELECT instance, STATUS, enabled, open_time, checkpoint_time FROM v$thread;

Configuración actual:

SHOW SGA;

SELECT * FROM v$sgainfo;

COL COMPONENT FORMAT A30
SELECT * FROM v$sga_dynamic_components;

SHOW parameter PGA;

or

SET pages 999;
COLUMN pga_size format 999,999,999
SELECT
    1048576+a.value+b.value   pga_size
FROM
   v$parameter a,
   v$parameter b
WHERE
   a.name = 'sort_area_size'
AND
   b.name = 'hash_area_size'
;

SELECT LOG_MODE FROM SYS.V$DATABASE;

SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;

ARCHIVE LOG LIST

SHOW parameter spfile ;

col VALUE format a200
SELECT name, VALUE FROM v$parameter WHERE name = 'spfile' ;

COL NAME FORMAT A200;
SELECT NAME FROM V$CONTROLFILE ;

o en RAC:

COL NAME FORMAT A200;
SELECT NAME FROM GV$CONTROLFILE ;

Y también:

COL VALUE FORMAT A200;
SELECT VALUE
FROM gv$parameter
WHERE name = 'control_files';

COL DIRECTORY_NAME FORMAT A40;
COL DIRECTORY_PATH FORMAT A180;
SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM all_directories ;

SHOW PARAMETER BACKGROUND_DUMP_DEST ; 

No he conseguido query para sacarlo, solo mediante variables de sistema:

$ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace/

Hay que cargar las variables de +ASM, por supuesto:

• $ASMDB: Normalmente se corresponde con: tolower(“+ASM”) | sed 's,+,,g'
• $DB1: Normalmente se corresponde con: tolower(“+ASM”)
• $ORACLE_SID: Normalmente se corresponde con “+ASM”

Se puede debugar otra sesión a la nuestra mediante:

oradebug setospid 3885
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro

SHOW parameter db_name ;

SELECT DBID FROM V$DATABASE;

En caso de que la bbdd esté down, podemos verlo en los logs de RMAN

$rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production ON Tue May 6 01:25:48 2008
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
connected TO target DATABASE: ARJU (DBID=2869417476)

Mas info: http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html

 SELECT   * FROM v$version ;

COL VALUE FORMAT A50
COL PARAMETER FORMAT A50
SELECT * FROM nls_database_parameters ;

col members format a150
col STATUS format a20
SELECT vl.group#,
    vl.thread#,
    vl.sequence#,
    vl.bytes/1024/1024 SIZE_MB,
    vl.archived,
    vl.status,
    gm.type STATUS,
    gm.members
FROM v$log vl, 
    (SELECT GROUP#, TYPE, listagg(vlf.member, ' ; ') WITHIN GROUP (ORDER BY member) AS members
        FROM v$logfile vlf  
    GROUP BY vlf.group#, TYPE) gm
WHERE
    vl.group#=gm.group#
;

SELECT * FROM v$log;

col member format a100
SELECT * FROM v$logfile ;

Dónde se envían los redos:

col DEST_NAME FORMAT A20;
COL STATUS FORMAT A15;
COL NAME_SPACE FORMAT A15;
COL SCHEDULE FORMAT A15;
COL DESTINATION FORMAT A30;
SELECT DEST_NAME, STATUS, NAME_SPACE, SCHEDULE, DESTINATION FROM V$ARCHIVE_DEST WHERE ROWNUM < 10 ;

Por regla general:

• SHOW parameter <param_name>

  in sql*plus

• SELECT VALUE FROM v$parameter WHERE name = LOWER('param_name') 

• SELECT VALUE FROM gv$parameter WHERE name = LOWER('param_name') 

ALTER SYSTEM RESET <PARAMETER_NAME> ;

This query will show ORL for ONLINE redo logs and SRL for STANDBY redo logs

COL member format 150
SELECT lf.group#,l_type.log_type AS TYPE, lf.member
FROM v$logfile lf
JOIN (
   SELECT GROUP#,'ORL' AS log_type FROM v$log
   UNION
   SELECT GROUP#,'SRL' AS log_type FROM v$standby_log) l_type
ON lf.group#=l_type.group#
ORDER BY lf.group#
/

 SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ;

COL NAME FORMAT A200
SELECT * FROM v$dbfile ORDER BY 1;

col FILE_NAME FORMAT A80;
COL tablespace_name FORMAT A30;
SELECT FILE_ID,
        file_name, 
        Tablespace_name, 
	bytes/(1024*1024) "Size MB", 
	MAXBYTES/(1024*1024) "MAX MB", 
	AUTOEXTENSIBLE "extensible"
FROM dba_data_files 
ORDER BY FILE_ID
;

or super simple:

SELECT file_name FROM dba_data_files ORDER BY 1;

CREATE SMALLFILE 
    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
    DATAFILE '/ruta/completa/al/datafile.dbf'
    SIZE 500M AUTOEXTEND 
    ON MAXSIZE 5000M
    ;

CREATE SMALLFILE 
    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
    DATAFILE '+ASMNAME'
    SIZE 500M AUTOEXTEND 
    ON MAXSIZE 5000M
   ;

SELECT dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') FROM dual ;

ALTER tablespace NOMBRE_DEL_TABLESPACE 
      ADD datafile /ruta/completa/al/datafile.dbf
      ;

SELECT name, total_mb, free_mb FROM v$asm_diskgroup_stat ;

o

SELECT  name, group_number, disk_number, total_mb, free_mb
FROM    v$asm_disk
ORDER   BY group_number
/

Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL:

COL TABLESPACE_NAME FORMAT A40
COL MAX_MB FORMAT 99999999999
COL REAL_FREE_MB FORMAT 99999999999
COL PERCENT FORMAT 999.99
SELECT TABLESPACE_NAME,
        MAX_MB,
        REAL_FREE_MB FREE_MB,
        MAX_MB-REAL_FREE_MB USED_MB,
        (MAX_MB-REAL_FREE_MB)/MAX_MB*100 "PERCENT"
FROM (
    SELECT MAXUSAGE.TABLESPACE_NAME,
            MAXUSAGE.MAX_MB,
            CASE WHEN MAXUSAGE.ACTUAL_DATAFILE_MB < MAXUSAGE.MAX_MB THEN
                MAX_MB-(ACTUAL_DATAFILE_MB-FREE_MB)
            ELSE
                FREE_MB
            END  REAL_FREE_MB
    FROM
        (
        SELECT TABLESPACE_NAME,
                SUM(CASE WHEN MAXBYTES > 0 THEN MAXBYTES ELSE BYTES END)/1024/1024 MAX_MB,
                SUM(BYTES)/1024/1024 ACTUAL_DATAFILE_MB
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
        ) MAXUSAGE,
        (
        SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_MB
            FROM dba_free_space
            GROUP BY TABLESPACE_NAME
        ) FREEUSAGE
    WHERE MAXUSAGE.TABLESPACE_NAME=FREEUSAGE.TABLESPACE_NAME)
;

Query “normal”:

SET PAGES 999
COL TABLESPACE_NAME FORMAT A40
COL "SIZE_MB" FORMAT 99999999999
COL "FREE_MB" FORMAT 99999999999
COL "OCCUPIED_MB" FORMAT 99999999999
COL "PERCENT" FORMAT 999
 
 
SELECT  USED.TABLESPACE_NAME, 
        CEIL(USED.USED_MB) "SIZE_MB", 
        USED.USED_MB-FREE.FREE_MB "OCCUPIED_MB", 
        DECODE(CEIL(FREE.FREE_MB),NULL,0,CEIL(FREE.FREE_MB)) "FREE_MB",
        DECODE(100 - CEIL(FREE.FREE_MB/USED.USED_MB*100),NULL,100,100 - CEIL(FREE.FREE_MB/USED.USED_MB*100)) "PERCENT"
FROM (
        SELECT  TABLESPACE_NAME, 
                SUM(BYTES)/1024/1024 USED_MB
        FROM DBA_DATA_FILES 
        GROUP BY TABLESPACE_NAME
        UNION ALL
        SELECT TABLESPACE_NAME || '**TEMP**', SUM(BYTES)/1024/1024 USED_MB
        FROM DBA_TEMP_FILES 
        GROUP BY TABLESPACE_NAME
    ) USED,
    (
        SELECT  TABLESPACE_NAME, 
                SUM(BYTES)/1024/1024 FREE_MB
        FROM DBA_FREE_SPACE 
        GROUP BY TABLESPACE_NAME
    ) FREE
WHERE USED.TABLESPACE_NAME=FREE.TABLESPACE_NAME
ORDER BY 1
/

Column format

COL OWNER FORMAT A10;
COL SEGMENT_NAME FORMAT A35;
COL TABLESPACE_NAME FORMAT A35;
COL TOTAL_MB FORMAT 99999990.9999 ;
COL SUM_BYTES FORMAT 999999999999999999.9999 ;

• Tables:

SELECT  SEGMENT_NAME,
        SUM(BYTES) SUM_BYTES,
        NVL(SUM(BYTES)/1024/1024,0.0) TOTAL_MB
FROM DBA_EXTENTS
WHERE OWNER='DATABASE_USER'
AND SEGMENT_TYPE='TABLE'
GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
ORDER BY SEGMENT_NAME,TABLESPACE_NAME
;

• Indexes:

SELECT  SEGMENT_NAME,
        SUM(BYTES) SUM_BYTES,
        NVL(SUM(BYTES)/1024/1024,0.0) TOTAL_MB
FROM DBA_EXTENTS
WHERE OWNER='DATABASE_USER'
AND SEGMENT_TYPE='INDEX'
GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
ORDER BY SEGMENT_NAME,TABLESPACE_NAME
;

Sort by size:

• Tables:

SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
    (
    SELECT  SEGMENT_NAME,
            SUM(BYTES) SUM_BYTES
    FROM DBA_EXTENTS
    WHERE OWNER='DATABASE_USER'
    AND SEGMENT_TYPE='TABLE'
    GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
    )
    ORDER BY SUM_BYTES
;

• Indixes:

SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
    (
    SELECT  SEGMENT_NAME,
            SUM(BYTES) SUM_BYTES
    FROM DBA_EXTENTS
    WHERE OWNER='DATABASE_USER'
    AND SEGMENT_TYPE='INDEX'
    GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
    )
    ORDER BY SUM_BYTES
;

Based on: How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1):

Se puede usar el AVG_ROW_LEN de DBA_TABLES, que es espacio medio en BYTES de cada row en la tabla.

Primero hay que lanzar el cálculo de stats:

ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS ;

Necesitamos también el tamaño de bloque:

 SHOW PARAMETER DB_BLOCK_SIZE

SELECT (
    (SELECT COUNT(*) FROM <TABLE_NAME>)
    /
    ((<DB_BLOCK_SIZE>-(<DB_BLOCK_SIZE>*(PCT_FREE/100)))/AVG_ROW_LEN)*<DB_BLOCK_SIZE>/1024/1024
    ) AS ESTIMATED_MB 
FROM DBA_TABLES
WHERE TABLE_NAME LIKE '<TABLE_NAME>' ;

SELECT segment_name, segment_type, bytes / 1024 / 1024 size_in_MB
FROM dba_segments
WHERE (
    owner = 'USERNAME' 
        AND segment_name = 'TABLE_NAME'
        OR segment_name IN (
            SELECT segment_name
            FROM all_lobs
            WHERE TABLE_NAME = 'TABLE_NAME'
            UNION
            SELECT index_name
            FROM all_lobs
            WHERE TABLE_NAME = 'TABLE_NAME'
            )
);

Based on AWR tables:

SELECT
    h.BEGIN_INTERVAL_TIME  || ';' || o.OBJECT_NAME || ';' || s.SPACE_USED_DELTA
FROM
    DBA_OBJECTS o,
    DBA_HIST_SNAPSHOT h,
    (
        SELECT
            SNAP_ID,
            TS#,
            OBJ#,
            SPACE_USED_DELTA
        FROM
            DBA_HIST_SEG_STAT
        WHERE SPACE_USED_DELTA >0
    ) s,
    v$tablespace t
WHERE
    s.OBJ# = o.OBJECT_ID
    AND   s.TS# = t.TS#
    AND   o.owner = 'TABLE_OWNER'
    AND   o.object_type = 'TABLE'
    AND s.SNAP_ID = h.SNAP_ID
ORDER BY
    1 DESC
;

col file_name format a150
 
SELECT
    file_id,
    file_name,
    tablespace_name,
    STATUS
FROM
    dba_temp_files;

CREATE TEMPORARY TABLESPACE TEMP tempfile '/u02/oradata/DBTEST/datafile/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED ;

ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend ON NEXT 512m maxsize unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ;

Based on the following docs:

• Doc ID 793380.1
• Doc ID 317441.1

Both based on trying to solve ORA-1652

Check the status of the sort segment utilization :

SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM v$sort_segment; 

And overall:

COL USERNAME FORMAT A33
COL SID_SERIAL FORMAT A15
COL OSUSER FORMAT A33
COL TABLESPACE FORMAT A40
COL sql_text format A100 word wrapped
 
SELECT a.username, 
    a.sid || ',' || a.serial# AS SID_SERIAL,
    a.osuser, 
    b.tablespace, 
    b.blocks, 
    c.sql_text
FROM v$session a, 
    v$tempseg_usage b, 
    v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

Resumido en MB:

SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP BY 
   A.tablespace_name, 
   D.mb_total
/

Export vars:

export ORACLE_BASE=/opt/ora11g
export ORACLE_SID=+ASM
export ORACLE_HOME=/opt/ora11g/product/11.1.0/asm
export TNS_ADMIN=/opt/ora11g/product/11.1.0/asm/network/admin
export PATH=/opt/ora11g/product/11.1.0/asm/bin:$PATH
sqlplus '/ as sysasm'

O usar el “*.env” por supuesto

Asm cli/wrapper:

asmcmd -p

COL PATH FORMAT A100
COL NAME FORMAT A40
COL FAILGROUP FORMAT A30
SELECT  NAME, 
        PATH,
        OS_MB, 
        TOTAL_MB, 
        FREE_MB, 
        STATE, 
        REDUNDANCY, 
        FAILGROUP
FROM V$ASM_DISK 
ORDER BY NAME
;

COLUMN DISK_NAME FORMAT A25;
COLUMN DISKGROUP FORMAT A20;
COLUMN CAPACITY FORMAT 999999999;
COLUMN TOTAL_MB FORMAT 999999999;
COLUMN FREE_MB FORMAT 999999999;
COLUMN SYS_PATH FORMAT A80;
SELECT D.NAME AS DISK_NAME,
	G.NAME AS DISKGROUP,
	D.OS_MB AS CAPACITY,
	D.TOTAL_MB AS TOTAL_MB,
	D.FREE_MB AS FREE_MB,
	D.STATE AS STATE,
	D.PATH AS SYS_PATH
FROM V$ASM_DISK D, V$ASM_DISKGROUP G
WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ;

COLUMN DISKGROUP FORMAT A20;
COLUMN CAPACITY_GB FORMAT 999999999.99;
COLUMN FREE_GB FORMAT 999999999.99;
SELECT G.NAME AS DISKGROUP,
    SUM(D.TOTAL_MB)/1024 AS CAPACITY_GB,
    SUM(D.FREE_MB)/1024 AS FREE_GB
FROM V$ASM_DISK D, V$ASM_DISKGROUP G
WHERE D.GROUP_NUMBER=G.GROUP_NUMBER 
GROUP BY G.NAME
;

COL NAME FORMAT A60
COL VALUE FORMAT A30
SELECT dg.name AS diskgroup,
        a.name,
        A.VALUE,
        READ_ONLY
FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a 
WHERE dg.group_number = a.group_number
ORDER BY DG.NAME
;

Para crear el diskgroup, debemos hacer un :

/etc/init.d/oracleasm createdisk NOMBRE_DEL_DISCO DISPOSITIVO

El dispositivo tiene que ser una partición de disco (no directamente un sdX)

Después, listamos el path desde ASM:

SELECT NAME, PATH  FROM V$ASM_DISK ;

Y con el path hacemos la query de create:

CREATE diskgroup DATA external redundancy disk
	'ORCL:PATH_DISK1',
	'ORCL:PATH_DISK2';

DROP DISKGROUP 'DISKGROUP_NAME'

O

DROP DISKGROUP 'DISKGROUP_NAME' INCLUDING CONTENTS

Documentación básica:

• Managing the ASM Cluster File System (ACFS)

Flash recovery area

SHOW parameter db_recovery;

 SELECT * FROM v$flash_recovery_area_usage;

y

COL NAME FORMAT A100;
SELECT NAME, 
       SPACE_LIMIT/1024/1024, 
       SPACE_USED/1024/1024, 
       NUMBER_OF_FILES  
FROM V$RECOVERY_FILE_DEST ;

COL NAME FORMAT A20;
COL VALUE FORMAT A60;
SELECT name, VALUE
FROM gv$parameter
WHERE name LIKE '%undo%';

Very simple:

SELECT tablespace_name, STATUS, SUM(blocks) * 8192/1024/1024/1024 GB 
FROM dba_undo_extents GROUP BY tablespace_name, STATUS;

SELECT  TOTALSPACE.TOTAL TOTAL_SPACE,
        NVL(USEDSPACE.USED,0.0) USED_SPACE,
        100 - trunc(NVL(USEDSPACE.USED,0.0)/TOTALSPACE.TOTAL * 1000) / 10 PERCENT_FREE
FROM (
    SELECT TABLESPACE_NAME, SUM(MAXBYTES)/1024/1024 TOTAL
    FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = (SELECT VALUE FROM v$parameter WHERE name LIKE 'undo_tablespace') 
    GROUP BY TABLESPACE_NAME
    ) TOTALSPACE
LEFT OUTER JOIN
    (
    SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 USED
    FROM DBA_UNDO_EXTENTS 
    WHERE (STATUS='UNEXPIRED' OR STATUS='ACTIVE')
        AND TABLESPACE_NAME = (SELECT VALUE FROM v$parameter WHERE name LIKE 'undo_tablespace') 
    GROUP BY TABLESPACE_NAME
    ) USEDSPACE
ON TOTALSPACE.TABLESPACE_NAME=USEDSPACE.TABLESPACE_NAME
;

col ROLL_NAME format a30
col userID format a40
col sid_serial format a15
COL PROGRAM FORMAT A50
COL MACHINE FORMAT A40
 
SELECT r.name roll_name,
         s.osuser || '/' || s.username userID,
         s.sid || '.' || s.serial# SID_SERIAL,
         s.program program,
         s.status STATUS,
         s.machine machine
   FROM v$lock l, v$rollname r, v$session s
   WHERE     s.sid = l.sid
         AND TRUNC (l.id1(+) / 65536) = r.usn
         AND l.TYPE(+) = 'TX'
         AND l.lmode(+) = 6
ORDER BY r.name
/

col "UNDO RETENTION [Sec]" format a30
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
;

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
;

ALTER TABLESPACE undotbs_01
ADD DATAFILE '+ASM' AUTOEXTEND ON NEXT 1G 
MAXSIZE UNLIMITED;

CREATE UNDO TABLESPACE undotbs_02
     DATAFILE '+ASM' SIZE 2G REUSE AUTOEXTEND ON;

ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;

Si estamos en Automatic UNDO management (lo normal), el parámetro UNDO_RETENTION es IGNORADO, y se gestiona dinámicamente basado en si el tablespace es fijo o dinámico e intentando mantener siempre la máxima retención en el UNDO y el umbral de alerta.

Para ver el auto tune:

COL begin_time format a30
col end_time format a30
col MAXQUERYID FORMAT A20
 
SELECT to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
       to_char(end_time, 'DD-MON-RR HH24:MI') end_time, 
       MAXQUERYLEN,
       MAXQUERYID,
       MAXCONCURRENCY,
       UNXPSTEALCNT STEALED_SEGMENTS,
       tuned_undoretention
FROM v$undostat
ORDER BY end_time;

(más simple):

SELECT to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
       to_char(end_time, 'DD-MON-RR HH24:MI') end_time, 
       tuned_undoretention
FROM v$undostat
ORDER BY end_time;

Cambiar UNDOTBS1 por el nombre del tablespace de UNDO:

DECLARE
    warning_operator binary_integer;
    warning_value varchar2(60);
    critical_operator binary_integer;
    critical_value varchar2(60);
    observation_period binary_integer;
    consecutive_occurrences binary_integer;
BEGIN
 
    dbms_server_alert.get_threshold(
        dbms_server_alert.tablespace_pct_full, 
        warning_operator, 
        warning_value,
        critical_operator, 
        critical_value, 
        observation_period,
        consecutive_occurrences,
        NULL,
        dbms_server_alert.object_type_tablespace, 
        object_name => 'UNDOTBS1');
 
    dbms_output.put_line('Warning operator: ' || warning_operator);
    dbms_output.put_line('Warning value: ' || warning_value);
    dbms_output.put_line('Critical operator: ' || critical_operator);
    dbms_output.put_line('Critical value: ' || critical_value);
    dbms_output.put_line('Observation_period: ' || observation_period);
    dbms_output.put_line('Consecutive occurrences:' || consecutive_occurrences);
END;
/

y:

COL METRICS_NAME FORMAT A30;
COL WARNING_OPERATOR FORMAT A16;
COL WARNING_VALUE FORMAT A30;
COL CRITICAL_OPERATOR FORMAT A16;
COL CRITICAL_VALUE FORMAT A8;
COL OPERATION_PERIOD FORMAT A10;
COL CONSECUTIVE_OCCURRENCES FORMAT 9999999999;
COL INSTANCE_NAME FORMAT A15;
COL OBJECT_NAME FORMAT A12;
COL OBJECT_TYPE FORMAT A10;
COL STATUS FORMAT A10;
 
SELECT * FROM DBA_THRESHOLDS ;

COL RDMAENABLE FORMAT A10
COL SVRNAME FORMAT A30
col DIRNAME FORMAT A120
SELECT * FROM V$DNFS_SERVERS ;

col path format a40
col LOCAL format a40
col SVRNAME format a40
SELECT pnum, svrname, path, LOCAL FROM v$dnfs_channels ;

COL FILENAME FORMAT A200
SELECT * FROM V$DNFS_FILES ;

• Todos:

SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS FROM dba_users ORDER BY 1 ;

• Activos:

SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';

col profile format a30
col resource_name format a50 ;
col LIMIT format a30
 
SELECT *
FROM DBA_PROFILES 
ORDER BY profile, resource_name ;

Esto se refiere a un “usuario” pero viene siendo lo mismo:

SELECT dbms_metadata.get_ddl(''USER'',''usuari'') FROM dual;

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
    SELECT NULL grantee, username granted_role FROM dba_users WHERE username LIKE UPPER('%&uname%')
  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
/

Plain mode:

SELECT GRANTED_ROLE FROM (
SELECT grantee, granted_role FROM dba_role_privs
  UNION
SELECT grantee, privilege FROM dba_sys_privs
)
WHERE GRANTEE='USERNAME';

Mega query para obtener lo que sea:

COL OBJECT_NAME FORMAT A35
COL OBJECT_TYPE FORMAT A25
COL OWNER FORMAT A33
COL GRANTOR FORMAT A33
COL GRANTEE FORMAT A33
COL PERMISSION_NAME FORMAT A50
SELECT  OBJECT_NAME, 
        OBJECT_TYPE, 
--         OWNER, 
        GRANTOR, 
        GRANTEE, 
--         PERMISSION, 
        PERMISSION_NAME
FROM (
    SELECT  OBJECT.NAME OBJECT_NAME,
            decode (OBJECT.TYPE#, 
                    0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 
                    3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                    7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                    11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 
                    14, 'TYPE BODY', 19, 'TABLE PARTITION', 
                    20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 
                    23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 
                    29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                    32, 'INDEXTYPE', 33, 'OPERATOR',
                    34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                    40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                    42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
                    44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                    48, 'CONSUMER GROUP', 55, 'XML SCHEMA', 
                    56, 'JAVA DATA', 57, 'EDITION', 59, 'RULE',
                    60, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT',
                    66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 
                    69, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 
                    79, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL', 
                    87, 'ASSEMBLY', 90, 'CREDENTIAL', 
                    92, 'CUBE DIMENSION', 93, 'CUBE', 
                    94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                    100, 'FILE WATCHER', 101, 'DESTINATION','UNDEFINED'
                    ) OBJECT_TYPE,
            U.NAME      OWNER, 
            UR.NAME     GRANTOR, 
            UE.NAME     GRANTEE,
            OA.PRIVILEGE#  PERMISSION,
            MIN(TPM.NAME) PERMISSION_NAME
    FROM    sys.objauth$ oa, 
            sys."_CURRENT_EDITION_OBJ" OBJECT,
            sys.USER$ U,
            sys.USER$ UR,
            sys.USER$ UE,
            sys.table_privilege_map TPM
    WHERE   OA.OBJ#=OBJECT.OBJ#
    AND     OA.COL# IS NULL
    AND     U.USER#=OBJECT.OWNER#
    AND     oa.grantor# = ur.USER#
    AND     oa.grantee# = ue.USER#
    AND     TPM.PRIVILEGE = OA.PRIVILEGE#
    GROUP BY OBJECT.NAME,U.NAME,OBJECT.TYPE#,UR.NAME,UE.NAME, OA.PRIVILEGE#
    )
WHERE GRANTOR='USERNAME'
AND GRANTEE='USERNAME'
AND OWNER='USERNAME'
AND OBJECT_TYPE='TABLE'
AND OBJECT_NAME='NAME'
ORDER BY OBJECT_NAME, PERMISSION_NAME
;

Aparte, los grants sobre objects se detallan en las vistas:

• table_privileges
• dba_role_privs

De CADA ESQUEMA. Es decir, los que concede SYS están en su esquema, los que concede ADMINUSER están en su propio esquema. Hay que hacer switch a cada esquema para conocer sus grants

• Grants directos:

SELECT owner, TABLE_NAME, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM table_privileges
 WHERE grantee = '&theUser'
 ORDER BY owner, TABLE_NAME;

• Grants INdirectos:

SELECT DISTINCT owner, TABLE_NAME, PRIVILEGE 
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = '&theUser'
 ORDER BY owner, TABLE_NAME;

SELECT * FROM DBA_TS_QUOTAS ORDER BY TABLESPACE_NAME, USERNAME;

CREATE USER "USERNAME"
    PROFILE "DEFAULT" 
    IDENTIFIED BY "********" DEFAULT TABLESPACE "TABLESPACE_DAT" 
    TEMPORARY TABLESPACE "TEMP" 
    ACCOUNT UNLOCK ;
 
GRANT CONNECT, RESOURCE TO "USERNAME" ;

Si el usuario ya existe/existía y lo que queremos es reutilizar su password, primero hemos de obtener el hash del password con el siguiente procedure:

SET serveroutput ON SIZE 200000
 
DECLARE
    stmt varchar2(200);
    v_old_hash USER$.password%TYPE;
    v_new_hash USER$.spare4%TYPE;
    v_hash varchar2(200);
BEGIN
   FOR user_rec IN (SELECT name, password, spare4 FROM USER$ WHERE TYPE#=1 AND astatus IN(0,1,2)) loop
      v_old_hash := user_rec.password;
      v_new_hash := user_rec.spare4;
      IF NOT ((v_old_hash IS NULL) AND (v_new_hash IS NULL)) THEN
         IF (v_new_hash IS NULL) THEN
             IF v_old_hash <> 'EXTERNAL' THEN
                 v_hash := ''''||v_old_hash||'''';
             ELSE
             GOTO end_loop;
         END IF;
      END IF;
      IF (v_old_hash IS NULL) THEN
          v_hash := ''''||v_new_hash||'''';
      END IF;
      IF ((v_old_hash IS NOT NULL) AND (v_new_hash IS NOT NULL)) THEN
          v_hash := ''''||v_old_hash||';'||v_new_hash||'''';
      END IF;
          stmt := 'alter user '||user_rec.name||' identified by values'||v_hash;
      END IF;
     dbms_output.put_line(stmt||';');
  <<end_loop>>
  NULL;
  END loop;
END;
/

La columna ASTATUS de USER$ la he limitado a 3 valores:

• 0 = ACCOUNT_STATUS OPEN
• 1 = ACCOUNT_STATUS EXPIRED
• 2 = ACCOUNT_STATUS EXPIRED(GRACE)

Y luego usarlo en la query:

CREATE USER "USERNAME"
    PROFILE "DEFAULT" 
    IDENTIFIED BY VALUES '<PASSWORD>;<SPARE4>'
    DEFAULT TABLESPACE "TABLESPACE_DAT" 
    TEMPORARY TABLESPACE "TEMP" 
    ACCOUNT UNLOCK ;

Podemos obtener previamente los datos necesarios (tablespace, profile…) con la query:

SELECT 'CREATE USER ' || USERNAME ||
    ' IDENTIFIED BY VALUES ''' || S.PASSWORD || ';' || S.SPARE4 ||
    ''' DEFAULT TABLESPACE ' || D.DEFAULT_TABLESPACE ||
    ' TEMPORARY TABLESPACE  ' || D.TEMPORARY_TABLESPACE ||
    ' PROFILE ' || D.PROFILE || 
    ' ACCOUNT UNLOCK ; '
    FROM DBA_USERS D, SYS.USER$ S 
    WHERE D.USER_ID=S.USER# 
    AND
    USERNAME = 'VOXELADMIN' ;

Se usa el combo '<11g password hash>;<10g password hash>' por que si especificamos HASH normal o SPARE4, el otro deja de existir (en caso de que exista).

Más info

DROP USER USERNAME cascade ;

Cuidado con el cascade por el tema de privilegios concedidos (puede seguir el cascade a otros usuarios).

ALTER SESSION SET CURRENT_SCHEMA="NOMBRE_USUARIO";

Entre comillas por que lo que se cambia así es el schema.

Verificar:

SELECT sys_context ('userenv', 'current_schema') FROM dual ;

ALTER USER username IDENTIFIED BY "PASSWORD" ;

ALTER USER USERNAME account UNLOCK;

Para clonar un usuario sin clonar los datos (remap desde export) se puede usar el siguiente script:

-- user_clone.sql
-- Andy Barry/A
-- 20/02/06
-- modified by dodger
-- 17/02/2010
 
SET LINES 999 pages 999
SET verify off
SET feedback off
SET heading off
 
SELECT username FROM dba_users ORDER BY username
/
 
undefine USER
 
accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '
 
SELECT username, created
FROM   dba_users
WHERE  LOWER(username) = LOWER('&newuser')
/
 
accept poo prompt 'Continue? (ctrl-c to exit)'
spool /tmp/user_clone_tmp.sql
 
SELECT 'create user ' || '&newuser' ||
       ' identified by ' || '&passwd' ||
       ' default tablespace ' || default_tablespace ||
       ' temporary tablespace ' || temporary_tablespace || ';' "user"
FROM   dba_users
WHERE  username = '&&userid'
/
 
 
SELECT 'alter user &newuser quota '||
       decode(max_bytes, -1, 'unlimited'
       ,                     CEIL(max_bytes / 1024 / 1024) || 'M') ||
       ' on ' || tablespace_name || ';'
FROM   dba_ts_quotas
WHERE  username = '&&userid'
/
 
SELECT 'grant ' ||granted_role || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
FROM   dba_role_privs
WHERE  grantee = '&&userid'
/
 
SELECT 'grant ' || privilege || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
FROM   dba_sys_privs
WHERE  grantee = '&&userid'
/
 
SELECT 'grant ' || privilege || ' to &newuser' ||
       decode(GRANTABLE, 'NO', ';', 'YES', ' with grant option;') "PRIV"
FROM   dba_tab_privs
WHERE  grantee = '&&userid'
/
 
 
spool off
 
undefine USER
 
SET verify ON
SET feedback ON
SET heading ON

Esto genera un script SQL “

/tmp/user_clone_tmp.sql

” que contiene todos los privilegios del usuario objetivo. Hay que tener en cuenta si queremos que el usuario tenga los datos en un tablespace diferente, para lo cual hay que cambiar el create user por supuesto.

SHOW parameter resource_limit ;

ALTER system SET RESOURCE_LIMIT=TRUE scope=BOTH;

CREATE PROFILE "LIMIT" LIMIT SESSIONS_PER_USER 2;

y modificar el usuario:

ALTER USER USERNAME PROFILE LIMIT ;

col type_name format a40
col type_oid format a40
col SUPERTYPE_NAME format a40
col SUPERTYPE_OWNER format a40
SELECT owner, 
  type_name, 
  type_oid, 
  SUPERTYPE_OWNER, 
  SUPERTYPE_NAME 
FROM dba_types
/

col type_name format a40
col type_oid format a40
 
SELECT owner,
  TABLE_NAME,
  column_name,
  data_type,
  virtual_column
FROM dba_tab_cols
WHERE data_type IN (
  SELECT
    type_name
  FROM dba_types
  WHERE NOT owner IN (
         'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
         'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
         'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
         'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
         'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
         'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
         'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
         'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
         'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
         'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
         'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
         'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
      )
  GROUP BY type_name
  )
/

Compile an object:

ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ;

• Paquete:

ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];

• Trigger:

ALTER TRIGGER [schema.]package_name COMPILE ;

Compile ALL objects (invalid): See: [SCRIPT] Recompile all database objects

Excluir las claúsulas de storage:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

Excluir

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);

Que escriba el terminador de objeto (muy recomendado):

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

No Incluir fk's:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);

No incluir constraints:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;

Lista de nombre de objects soportados por DBMS_METADATA.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;

Buscar OBJ# (object identifier):

col name format a40
col subname format a40
 
SELECT obj#,
  dataobj#,
  name,
  subname,
  CTIME ,
  MTIME,
  STIME ,
  STATUS
FROM obj$
WHERE name LIKE '%OBJECT_NAME%';

Buscar SOURCE:

SELECT *
FROM SOURCE$
WHERE OBJ# IN
  ( 
  SELECT obj# FROM obj$ WHERE name LIKE '%OBJECT_NAME%'
  ) ;

SELECT TABLE_NAME FROM dict WHERE TABLE_NAME LIKE 'V$%' ;

SELECT name FROM V$FIXED_TABLE ;

No existe ninguna tabla que determine el nombre de los objects, se pueden obtener (más o menos así):

SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ;

Cualquier owner:

SELECT OBJECT_NAME, OWNER, OBJECT_TYPE
FROM SYS.ALL_OBJECTS
WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE'
;

Con un owner determinado:

SELECT OBJECT_NAME, OWNER, OBJECT_TYPE
FROM SYS.ALL_OBJECTS
WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE'
AND OWNER LIKE 'OWNER'
;

Varios tipos

SELECT   * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') ;

Se pueden listar:

SELECT TABLE_NAME FROM all_tables ;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE '%username%';

SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%EXAMPLE%' ;

SELECT index_name FROM dba_indexes WHERE TABLE_NAME='tablename'; 

Y la descripción del índice:

SELECT DBMS_METADATA.GET_DDL('INDEX','INDEXNAME','SCHEMANAME') FROM DUAL;

O una descripción rápida:

COL INDEX_OWNER  FORMAT A30 ;      
COL INDEX_NAME  FORMAT A30 ;       
COL TABLE_OWNER  FORMAT A30 ;      
COL TABLE_NAME  FORMAT A30 ;       
COL COLUMN_NAME  FORMAT A30 ;      
COL COLUMN_POSITION  FORMAT 9999999
COL COLUMN_LENGTH  FORMAT 9999999
COL CHAR_LENGTH  FORMAT 9999999
COL DESCEND  FORMAT A30 ;          
SELECT * FROM ALL_IND_COLUMNS WHERE OWNER='<USERNAME>' AND TABLE_NAME='<TNAME>' ;

COL OWNER FORMAT A30;
COL TABLE_NAME FORMAT A30;
COL COLUMN_NAME FORMAT A30;
COL DATA_TYPE FORMAT A50;
SELECT OWNER, 
        TABLE_NAME, 
        COLUMN_NAME, 
        DATA_TYPE, 
        DATA_LENGTH 
FROM ALL_TAB_COLUMNS 
WHERE OWNER = '<USERNAME>'
ORDER BY OWNER,TABLE_NAME ;

COL OWNER FORMAT A30;
COL TABLE_NAME FORMAT A30;
COL COLUMN_NAME FORMAT A30;
COL virtual_column FORMAT A5;
 
SELECT owner,
  TABLE_NAME,
  column_name,
  virtual_column
FROM dba_tab_cols
WHERE VIRTUAL_COLUMN='YES'
AND OWNER IN ( 'USERNAME1', 'USER2' )
ORDER BY owner, TABLE_NAME, column_name
/

Not owned by system users:

COL OWNER FORMAT A30;
COL TABLE_NAME FORMAT A30;
COL COLUMN_NAME FORMAT A30;
COL virtual_column FORMAT A5;
 
SELECT owner,
  TABLE_NAME,
  column_name,
  virtual_column
FROM dba_tab_cols
WHERE VIRTUAL_COLUMN='YES'
AND NOT owner IN (
       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
    )
ORDER BY owner, TABLE_NAME, column_name
/

SELECT TABLE_OWNER, TABLE_NAME 
FROM  ALL_TAB_PARTITIONS 
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM') 
GROUP BY TABLE_OWNER, TABLE_NAME ;

Más info:

col table_owner format a20
col COMPRESS_FOR format a30
 
SELECT
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
TABLESPACE_NAME,
LOGGING,
COMPRESSION,
COMPRESS_FOR,
AVG_ROW_LEN
FROM DBA_TAB_PARTITIONS 
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY PARTITION_NAME
/

COL TABLE_OWNER FORMAT A20;
COL TABLE_NAME FORMAT A20;
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_POSITION, NUM_ROWS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
AND NUM_ROWS IS NOT NULL
ORDER BY PARTITION_POSITION DESC, TABLE_NAME ASC ;

dead

• https://netfiles.uiuc.edu/jstrode/www/oradd/dict_catagories.html

Encontrar Tablas Maestras/padres/primarias (tablas SIN fk's y referenciadas por otras tablas):

SELECT TABLE_NAME
FROM dba_CONSTRAINTS
WHERE owner='<USERNAME>'
AND CONSTRAINT_NAME IN (
    SELECT R_CONSTRAINT_NAME
    FROM dba_CONSTRAINTS
    WHERE owner='<USERNAME>'
    AND CONSTRAINT_TYPE='R'
    )
AND NOT TABLE_NAME IN (
    SELECT TABLE_NAME
    FROM dba_CONSTRAINTS
    WHERE owner='<USERNAME>'
    AND CONSTRAINT_TYPE='R'
    )
ORDER BY TABLE_NAME
/

COL NAME FORMAT A35
COL OWNER FORMAT A30
COL TYPE FORMAT A30
COL REFERENCED_OWNER FORMAT A30
COL REFERENCED_TYPE FORMAT A30
COL DEPENDENCY_TYPE FORMAT A20
COL REFERENCED_NAME FORMAT A33
SELECT 
   owner, 
   TYPE, 
   name,
   referenced_owner, 
   referenced_type, 
   referenced_name
FROM 
   dba_dependencies
WHERE 
   ((owner LIKE UPPER('&1') AND name LIKE UPPER('&2')) 
   OR
   (referenced_owner LIKE UPPER('&1') AND referenced_name LIKE UPPER('&2') ))
   AND 
   referenced_owner != 'SYS'
   AND 
   referenced_type != 'NON-EXISTENT'
ORDER BY 
   owner, TYPE, name;

Ejecutar:

@${ORACLE_HOME}/rdbms/admin/utldtree.sql

Después ejecutar para el objects que queramos, el procedure:

EXEC deptree_fill('object_type', 'object_owner', 'object_name');

y ver el resultado:

SELECT * FROM ideptree;

Link

The attached SQL file will show all dependencies for an object, both forwards and backwards through the heirarchy of objects in DBA_DEPENDENCIES, along with the LAST_DDL_TIME of all children.

The LAST_DDL_TIME should help track down why an object became invalid.

I'm fairly sure this query will not work in 8i. :(

You can circumvent that by logging into some account other than SYS and creating a table DBA_DEPENDENCIES. (it doesn't work in 8i due to queries on complex views and 'connect by' not working)

create table scott.dba_dependencies as select * from sys.dba_dependencies ;

--
Jared Still
Certifiable Oracle DBA AND Part TIME Perl Evangelist
-- dba_dependencies.sql
-- jkstill@xxxxxxxxx
-- base query from Jacques Kilchoer
-- 11/07/2006 - jkstill - added no_merge hints
--   encapsulated into inline view
--   added 'level'
--   display child DDL time
--
-- call on the command line:
--   @dba_dependencies <OWNER> <OBJECT>
-- if not on the command line, user will be
-- prompted for values
 
 
@clears
@COLUMNS
 
prompt Dependencies FOR Owner?:
 
col cowner noprint new_value uowner
SET term off feed off
SELECT UPPER('&1') cowner FROM dual;
SET term ON feed ON
 
prompt Dependencies FOR Object?:
 
col cobject noprint new_value uobject
SET term off feed off
SELECT UPPER('&2') cobject FROM dual;
SET term ON feed ON
 
SET line 142 pages 60
 
COLUMN display_parent format a58
COLUMN display_child format a58
COLUMN referenced_owner noprint
COLUMN referenced_object noprint
COLUMN referenced_type noprint
COLUMN owner noprint
COLUMN object noprint
COLUMN TYPE noprint
COLUMN last_ddl_time format a22 head 'CHILD DDL TIME'
 
undef 1 2
 
WITH dependencies AS (
        -- top down through the heirarchy
        SELECT /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' AS parent,
                TYPE || ' "' || owner || '"."' || name || '"' AS child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, TYPE
        FROM dba_dependencies
        START WITH
                referenced_owner = '&&uowner'
                AND referenced_name = '&&uobject'
        CONNECT BY
                referenced_owner = prior owner
                AND referenced_name = prior name
                AND referenced_type = prior TYPE
        UNION
        -- bottom up through the heirarchy
        SELECT /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' AS parent,
                TYPE || ' "' || owner || '"."' || name || '"' AS child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, TYPE
        FROM dba_dependencies
        START WITH
                owner = '&&uowner'
                AND name = '&&uobject'
        CONNECT BY
                owner = prior referenced_owner
                AND name = prior referenced_name
                AND TYPE = prior referenced_type
        ORDER BY 1, 2
)
SELECT lpad(' ',2*d.hlevel,' ') || d.parent display_parent, d.child 
display_child, o.last_ddl_time 
FROM dependencies d, dba_objects o
WHERE o.owner = d.owner
AND o.object_type = d.type
AND d.name = o.object_name
ORDER BY parent, child
/

COL OWNER FORMAT A30
COL R_OWNER FORMAT A30
COL CONSTRAINT_NAME FORMAT A50
COL R_CONSTRAINT_NAME FORMAT A50
 
SELECT OWNER,
        CONSTRAINT_NAME,
        CONSTRAINT_TYPE,
        TABLE_NAME,
        R_OWNER,
        R_CONSTRAINT_NAME,
        STATUS
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME LIKE '%TABLE_NAME%'
;

COL SCHEMA_NAME FORMAT A40 
COL REFERENCED_SCHEMA_NAME FORMAT A40 
COL REFERENCED_NAME FORMAT A40 
col REFERENCED_TYPE  format a30 
 
SELECT owner AS schema_name,
       name AS view_name,
       referenced_owner AS referenced_schema_name,
       referenced_name,
       referenced_type
FROM sys.dba_dependencies
WHERE TYPE = 'VIEW'
-- AND OWNER='OWNER'
-- and name LIKE '%VIEW_NAME%'
ORDER BY owner, name, referenced_name, referenced_owner, referenced_type
/

• http://docs.oracle.com/cd/B28359_01/server.111/b28318/dependencies.htm
• http://www.dba-oracle.com/d_dba_dependencies.htm

SELECT	rpad(c.name||':',11)||
	rpad(' current logons='||(to_number(b.sessions_current)),20)||
	'cumulative logons='||rpad(substr(a.value,1,10),10)||
	'highwater mark='||b.sessions_highwater Information
FROM
   v$sysstat a,
   v$license b,
   v$database c
WHERE
   a.name = 'logons cumulative' ;

col ID format a15;
col USERNAME FORMAT A30 ;
col OSUSER FORMAT A40 ;
COL MACHINE FORMAT A20 WORD WRAPPED;
COL "SID,SERIAL" FORMAT A15;
SELECT USERNAME, 
        SID || ',' || SERIAL# "SID,SERIAL", 
        STATUS, 
        OSUSER, 
        MACHINE, 
        SQL_ID, 
        LAST_CALL_ET "Last Activity"
FROM v$session
WHERE username IS NOT NULL
ORDER BY STATUS DESC, last_call_et DESC
/

COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN num_of_sessions FORMAT 99999999999 ;
SELECT ses.username username,
        COUNT(*) num_of_sessions
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
GROUP BY ses.username
ORDER BY num_of_sessions
/

COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
col client_info format a40 ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
ORDER BY SES.MACHINE
/

Con usuario inicializado:

COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
ORDER BY SES.MACHINE
/

Más:

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a80
SELECT s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY to_number(p.spid)
/

Con status=ACTIVE y SIN sqltext

SET linesize 280;
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
col OSUSER format a30 ;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Con status=ACTIVE

SET linesize 280;
COLUMN SID FORMAT 99999 ;
COLUMN SERIAL FORMAT 999999 ;
COLUMN OSPID FORMAT A5;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT SES.SID SID,
      SES.SERIAL# SERIAL,
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.P1TEXT P1TEXT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS,
      SQL.SQL_TEXT QUERY
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Mediante SQL_ID:

SET linesize 280;
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.P1TEXT P1TEXT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS,
      SQL.SQL_TEXT QUERY
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.SQL_ID IS NOT NULL
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/

SELECT username, MACHINE, last_call_et seconds, STATUS
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et
/

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USUARIO';

ALTER system KILL SESSION 'SID,SERIAL' ;

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE ; ' 
FROM V$SESSION 
WHERE USERNAME IN ('USERNAME1', 'USERNAMEn')

• v$lock based:

SELECT 'SID ' || l1.sid || ' is blocking ->' || l2.sid blocking
FROM v$lock l1, v$lock l2
WHERE l1.block =1 AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2
/

SELECT L1.SID, L2.SID
FROM V$LOCK L1, V$LOCK L2
WHERE 
   L1.BLOCK=1 
   AND L2.REQUEST > 0 
   AND L1.ID1=L2.ID1 
   AND L1.ID2=L2.ID2 ;

• v$session based:

col wait_class format a40
col event format a60
 
SELECT
    sid || '.' || serial# sid_serial,
    username,
    osuser,
    wait_class_id,
    wait_class#,
    wait_class,
    event
FROM
    v$session
WHERE
        state = 'WAITING'
    AND
        wait_class = 'Concurrency';

Formateo:

COL USERNAME FORMAT A23
COL OSUser FORMAT A23
COL Machine FORMAT A33
COL mode_held FORMAT A23
COL mode_requested FORMAT A23
COL lock_type format A25
COL lock_id1 format A10
COL lock_id2 format a10
COL object_name format a33
col object_type format a20

• Tx enqueue ready2kill:

COLUMN SID_SERIAL FORMAT A15 ;
SELECT
    lk.sid || ',' || se.serial# AS SID_SERIAL,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
  TO_CHAR(lk.id1) lock_id1,
  TO_CHAR(lk.id2) lock_id2,
  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  v$session se
WHERE
  lk.type  = 'TX'
AND lk.SID = se.SID
;

• Transaction enqueue (original):

SELECT
  lk.SID,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
  TO_CHAR(lk.id1) lock_id1,
  TO_CHAR(lk.id2) lock_id2,
  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  v$session se
WHERE
  lk.type  = 'TX'
AND lk.SID = se.SID
;

• DML enqueue & User supplied:

SELECT
  lk.SID,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
  DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
  TO_CHAR (lk.id1) lock_id1,
  TO_CHAR (lk.id2) lock_id2,
  ob.owner,
  ob.object_type,
  ob.object_name,
  DECODE(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  dba_objects ob,
  v$session se
WHERE
  lk.TYPE IN ('TM','UL')
AND lk.SID = se.SID
AND lk.id1 = ob.object_id
;

Sesiones que inician la cadena de bloqueos:

SELECT SID || ',' || SERIAL#
FROM V$SESSION
WHERE SID IN (
    SELECT l1.sid
    FROM v$lock l1, v$lock l2
    WHERE l1.block =1 AND l2.request > 0
    AND l1.id1=l2.id1
    AND l1.id2=l2.id2
    AND l1.id1 NOT IN (
        SELECT l2.sid
        FROM v$lock l1, v$lock l2
        WHERE l1.block =1 AND l2.request > 0
        AND l1.id1=l2.id1
        AND l1.id2=l2.id2
        )
    )
/

Extrictamente bloqueadores (sesiones que bloquean otras):

SELECT * FROM DBA_BLOCKERS ;

Mirar objects que se están accediendo:

col object format a50 ;
col TYPE format a20 ;
col owner format a20 ;
SELECT * FROM V$ACCESS WHERE TYPE LIKE 'TYPE'  ;

Y el propio bloqueo en sí:

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DDL_LOCKS ;

Casi lo mismo que el anterior:

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DML_LOCKS ;

• http://dbamohsin.wordpress.com/2011/07/19/t-sql-session-locks-blocks-waits/

SELECT TO_CHAR(m.END_TIME,(''DD-MM-YYYY HH24:MI:SS'')) e_dtm, m.intsize_csec/100 ints, s.username usr, m.session_id sid, m.session_serial_num ssn,
	ROUND(m.cpu) cpu100, m.physical_reads prds, m.logical_reads lrds, m.pga_memory pga, m.hard_parses hp, m.soft_parses sp, m.physical_read_pct prp,
	m.logical_read_pct lrp
FROM v$sessmetric m, v$session s
WHERE (m.physical_reads > 100 OR m.cpu > 100 OR m.logical_reads > 100) AND m.session_id = s.sid  AND m.session_serial_num = s.serial#
ORDER BY m.physical_reads DESC, m.cpu DESC, m.logical_reads DESC;

A partir del *SID*, podemos saber qué _query_ están lanzando mediante:

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
AND c.sid = &SID
ORDER BY  c.sid, d.piece;

SET feedback off
SET serveroutput ON SIZE 9999
COLUMN username format a20
COLUMN sql_text format a55 word_wrapped
BEGIN
  FOR x IN
   (SELECT username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') CURRENT_TIME,
    sql_address,
    sql_hash_value
   FROM v$session
   WHERE STATUS = 'ACTIVE'
   AND rawtohex(sql_address) <> '00'
   AND username IS NOT NULL ) loop
   FOR y IN (SELECT sql_text
   FROM v$sqlarea
   WHERE address = x.sql_address ) loop
   IF ( y.sql_text NOT LIKE '%listener.get_cmd%' AND
    y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%' ) THEN
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   END IF;
  END loop;
 END loop;
END;
/

Las querys con duración de más de 10 segundos:

COL SQL_TEXT FORMAT A130 WORD WRAPPED
SELECT * FROM
    (
    SELECT  SQL_ID, 
            CPU_TIME/NVL(EXECUTIONS,1) TIME_PER_EXECUTION,
            CPU_TIME, 
            EXECUTIONS,
            SQL_TEXT
    FROM V$SQLAREA
    WHERE EXECUTIONS>10
    ORDER BY EXECUTIONS DESC, TIME_PER_EXECUTION DESC, CPU_TIME DESC
    )
WHERE ROWNUM <31;

Ver también:

• V$SQL
• V$SESSION

La vista LONGOPS nos da información de las operaciones largas/costosas.

• Burleson info.
• Official view information.

COL UNITS FORMAT A12
COL opname FORMAT A30
COL target FORMAT A30
COL message FORMAT A70
COL SID_SERIAL FORMAT A15
 
SELECT * FROM
(
  SELECT
     opname,
     start_time,
     target,
     sofar,
     totalwork,
     units,
     elapsed_seconds,
     message
   FROM
        v$session_longops
  ORDER BY start_time DESC
)
WHERE rownum <=1;

COL SQL_ID FORMAT A15
COL NAME FORMAT A30
COL POSITION FORMAT 999
COL DATATYPE_STRING FORMAT A20
COL VALUE_STRING FORMAT A100
SELECT SQL_ID,
        LAST_CAPTURED,
        HASH_VALUE,
        NAME,
        POSITION,
        DUP_POSITION,
        DATATYPE_STRING,
        VALUE_STRING
FROM  V$SQL_BIND_CAPTURE 
WHERE  sql_id='SQL_ID' 
ORDER BY 1,2,4
;

The display_cursor funcion has the following undocumented format options:

• ALL
• ADVANCED
• OUTLINE
• PROJECTION

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ADVANCED');

Information about the formatting ops

Capturar la query:

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

y

SET LINESIZE 280
SET PAGESIZE 999
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT * FROM TABLE (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

Primero, hay que saber el SQL_ID y el PLAN_HASH_VALUE, podemos verlos con la siguiente query al SQLAREA:

SELECT sql_id, sql_text, hash_value, plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%SOMETHING HERE%'
/

Después, ejecutar el siguiente proceseo:

vari rc NUMBER
EXEC :rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=> '<SQL_ID>',plan_hash_value=> <PLAN_HASH_VALUE>) ;

Substituir:

• <SQL_ID>
• <PLAN_HASH_VALUE>

Habitualmente con eso vale, auqnue a lo mejor hay que hacer “algo más”.

Obtener los valores necesarios para borrarlo (los hashes):

COL SQL_HANDLE FORMAT A50
COL PLAN_NAME FORMAT A50
SELECT sql_handle, plan_name, enabled, accepted,fixed,origin FROM dba_sql_plan_baselines ;

Ejecutar el siguiente procedimiento para borarrlo:

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => '<SQL_HANDLE>',
    plan_name  => '<SQL_PLAN_NAME>');
 
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

• http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php
• http://fordba.wordpress.com/tag/dbms_spm-alter_sql_plan_baseline/
• http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm

• How to Identify Hard Parse Failures (Doc ID 1353015.1)
• [DOC] Kill hardparse

Esta query se basa en las estadísticas guardadas en el repositorio awr.

COL CPU_RANK FORMAT 999 ;
COL TOTAL_CPU_TIME FORMAT 999999999 ;
COL SQLID FORMAT A23 ;
SELECT CPU_RANK, SQLID, TOTAL_CPU_TIME, MODULE
FROM (
    SELECT  s.sql_id SQLID, 
            RANK() OVER (ORDER BY (MAX(s.CPU_TIME_TOTAL)) DESC) cpu_rank,
            ROUND(MAX(S. CPU_TIME_TOTAL)/1000000) TOTAL_CPU_TIME,
            MODULE
    FROM
    dba_hist_sqlstat s,
    dba_hist_snapshot sn
    WHERE
       sn.snap_id=s.snap_id
    GROUP BY
       s.sql_id, s.MODULE
   )
WHERE cpu_rank <=100
;

Full:

SELECT POOL, NAME, BYTES/1024/1024 FROM v$sgastat ;

Únicamente lo libre:

SELECT POOL, NAME, BYTES/1024/1024 FROM v$sgastat WHERE name LIKE '%free memory%' ;

COL BEGIN_INTERVAL_TIME FORMAT A50
COL END_INTERVAL_TIME FORMAT A50
COL NAME FORMAT A50
  SELECT sn.BEGIN_INTERVAL_TIME,
         SN.END_INTERVAL_TIME,
         sg.NAME,
         sg.POOL,
         sg.BYTES
    FROM dba_hist_snapshot sn, DBA_HIST_SGASTAT sg
   WHERE     sn.SNAP_ID = sg.SNAP_ID
         AND POOL = 'shared pool'
         AND NAME LIKE '%free memory%'
ORDER BY BEGIN_INTERVAL_TIME
;

SELECT 'Buffer Cache' NAME,
       ROUND (
            (congets.VALUE + dbgets.VALUE - physreads.VALUE)
          * 100
          / (congets.VALUE + dbgets.VALUE),
          2)
          VALUE
  FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
 WHERE     congets.NAME = 'consistent gets'
       AND dbgets.NAME = 'db block gets'
       AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
       DECODE (
          SIGN (
             ROUND (
                  (ec.VALUE - pc.VALUE)
                * 100
                / DECODE (ec.VALUE, 0, 1, ec.VALUE),
                2)),
          -1, 0,
          ROUND (
             (ec.VALUE - pc.VALUE) * 100 / DECODE (ec.VALUE, 0, 1, ec.VALUE),
             2))
  FROM v$sysstat ec, v$sysstat pc
 WHERE     ec.NAME = 'execute count'
       AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
       ROUND (
            ms.VALUE
          / DECODE ( (ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
          * 100,
          2)
  FROM v$sysstat ds, v$sysstat ms
 WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
  FROM v$librarycache
 WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
       ROUND ( (SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
  FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
       ROUND ( (SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
  FROM v$latch;

SET pagesize 132
 
COLUMN owner format a16
COLUMN name  format a36
COLUMN sharable_mem format 999,999,999
COLUMN executions   format 999,999,999

COLUMN name format 45
SELECT  owner, name||' - '||TYPE name, sharable_mem FROM v$db_object_cache
WHERE sharable_mem > 10000
  AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY sharable_mem DESC
/

SELECT  owner, name||' - '||TYPE name, loads , sharable_mem FROM v$db_object_cache
WHERE loads > 3
  AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY loads DESC
/

SELECT  owner, name||' - '||TYPE name, executions FROM v$db_object_cache
WHERE executions  > 100
  AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY executions  DESC
/

* Automated Workload Repository (awr)

DEGREE in dba_* views is VARCHAR!

Object parallelism ignoring degree 0 or 1

SELECT
  owner,
  INDEX_NAME,
  degree
FROM
  DBA_INDEXES
WHERE
  NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' )
;

Object parallelism ignoring degree 0 or 1

SELECT OWNER,
    TABLE_NAME,
    DEGREE
FROM
    DBA_TABLES
WHERE
    NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' )
;

ALTER DATABASE ADD LOGFILE GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ;

Hay que tener en cuenta los thread groups (rac), si tenemos varios thread groups, habrá que añadirlo al thread correspondiente:

ALTER DATABASE ADD LOGFILE thread 1 GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ;

Hay veces que show err no muestra todos los errores:

SELECT * FROM user_errors WHERE name LIKE '%OBJECT_NAME%' ;

Desde SYS pueden no aparecer los errores, hay que conectarse con el usuario.

CREATE DIRECTORY <IDENTIFIER> AS '/real/path/of/disk';

SELECT A.*, Round(A.COUNT#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
    (
    SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, COUNT(1) COUNT#, MIN(RECID) MIN#, MAX(RECID) MAX#
    FROM v$log_history
    GROUP BY To_Char(First_Time,'YYYY-MM-DD')
    ORDER BY 1 DESC
    ) A,
    (
    SELECT Avg(BYTES) AVG#
    FROM v$log
    ) B
    ORDER BY DAY
;

In order to track the amount of redo read from the in-memory log buffer vs. the amount of redo read from disk, a log buffer hit rate histogram has also been implemented. A new x$ table (x$logbuf_readhist) was added to display the histogram information. There are 5 columns of interest that can be obtained using the following query:

COL BUFSIZE FORMAT A30
COL BUFINFO FORMAT A30
SELECT BUFSIZE, RDMEMBLKS, RDDISKBLKS, HITRATE, BUFINFO FROM X$LOGBUF_READHIST;

It returns the following columns:

• BUFSIZE: the log buffer size in Kbytes. If “A” stands for the current log buffer size (as specified by the LOG_BUFFER parameter), then the values of BUFSIZE in the view are in the range of [0.5A, 2A] increasing by 10% for each row. There are a total of 16 rows.
• BUFINFO: identifies the ‘current’ log buffer and ‘target’ log buffer. The log buffer with BUFINFO equal to 'CURRENT' is the currently configured buffer size.
• RDMEMBLKS: number of blocks read from memory.
• RDDISKBLKS: number of blocks read from disk.
• HITRATE: calculated by 100 * RDMEMBLKS / (RDMEMBLKS + RDDISKBLKS)

In the histogram, the data in the row pointed to by CURRENT is captured while system is running. RDMEMBLKS in that row stands for the number of blocks actually read from log buffer in memory, and RDDISKBLKS stands for the number of blocks actually read from ORL on disk. The HITRATE is calculated correspondingly. The data in all other rows are calculated based on heuristics. RDMEMBLKS in those rows stands for the number of blocks that would be read from the log buffer should the buffer size be increased or reduced. If the hit ratio is less than 100%, then if possible, increase the buffer size to the value needed to achieve a 100% hit rate.

(Doc ID 951152.1)

SELECT to_date(first_time) DAY,
    to_char(SUM(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
    to_char(SUM(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
    to_char(SUM(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
    to_char(SUM(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
    to_char(SUM(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
    to_char(SUM(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
    to_char(SUM(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
    to_char(SUM(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
    to_char(SUM(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
    to_char(SUM(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
    to_char(SUM(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
    to_char(SUM(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
    to_char(SUM(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
    to_char(SUM(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
    to_char(SUM(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
    to_char(SUM(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
    to_char(SUM(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
    to_char(SUM(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
    to_char(SUM(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
    to_char(SUM(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
    to_char(SUM(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
    to_char(SUM(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
    to_char(SUM(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
    to_char(SUM(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
WHERE to_date(first_time) > sysdate -8
GROUP BY to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
ORDER BY to_date(first_time)
/

ALTER system checkpoint ;

Stop/shutdown de emergencia cuando nada más funciona:

sqlplus -prelim "/ as sysdba"
shutdown abort

Para localizar la query, tenemos que tener el SQL_ID que aparece en el alert y esperar que la query no se haya ido de memoria.

SELECT SQL_FULLTEXT FROM v$sql WHERE sql_id LIKE 'IDENTIFICADOR_DE_QUERY' ;

shutdown abort;
startup mount exclusive RESTRICT; 
DROP DATABASE;
exit ;

Información completa:

COL NAME FORMAT A60
COL VALUE FORMAT A30
COL DEFLT FORMAT A30
COL TYPE FORMAT A10
COL description FORMAT A120
SELECT  a.ksppinm name,
        b.ksppstvl VALUE,
        b.ksppstdf deflt,
        decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) TYPE,
        a.ksppdesc description
FROM sys.x$ksppi a, sys.x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm LIKE '\_%' escape '\'
order by name ;

únicamente nombre y descripción:

COL KSPPINM FORMAT A60
COL ksppdesc FORMAT A120
SELECT ksppinm, ksppdesc
FROM  x$ksppi 
WHERE substr(ksppinm,1,1) = '_'
ORDER BY  1,2;

DATABASE LINKS!

sqlplus SYSTEM

CREATE DATABASE LINK VOXEL CONNECT TO SYSTEM IDENTIFIED BY ******* USING 'ORIGEN';

SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN owner FORMAT A30
COLUMN db_link FORMAT A50
COLUMN username FORMAT A30
COLUMN host FORMAT A30
 
SELECT owner,
       db_link,
       username,
       host
FROM   dba_db_links
ORDER BY owner, db_link
/

DROP DATABASE LINK <link_name>;

o

DROP PUBLIC DATABASE LINK <link_name>;

DECLARE
   c1 NUMBER;
   c2 NUMBER;
   c3 NUMBER;
--    ressult testing_binds%ROWTYPE;
--    inttype NUMBER := 4001;
   theSQL    VARCHAR2(2000);
BEGIN
    theSQL := '
    SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "T_ITS", SCALE_ROWS=21196.12092) */
    c1,
    c2,
    c3
FROM
    (
        SELECT /*+ qb_name("innerQuery") INDEX( "T_ITS" "IT_TBUFFER_SEARCH_UX3_R") */
            COUNT(*) AS c1,
            4294967295 AS c2,
            SUM(
                CASE
                    WHEN("T_ITS"."INTTYPE" =:b1) THEN 1
                    ELSE 0
                END
            ) AS c3
        FROM
            ciberterminal."T_IT_TBUFFER_SEARCH" "T_ITS"
        WHERE
            ( "T_ITS"."INTTYPE" =:b1 )
    ) innerquery' ;
    EXECUTE IMMEDIATE theSQL INTO c1, c2, c3 USING 4001, 4001;
END;
/

DECLARE
    RESSULT VARCHAR(100);
BEGIN
  FOR tname IN (
    SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OWNER='SYS' AND OBJECT_TYPE='TABLE'
  )
  LOOP
    SELECT REGEXP_SUBSTR(DDL, '.*TABLESPACE.*') INTO RESSULT FROM (SELECT DBMS_METADATA.GET_DDL('TABLE', tname.object_name, 'SYS' ) DDL FROM DUAL) ;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(RESSULT));
  END LOOP;
END;
/

Sometimes you want to have feedback of the PL/SQL output. DMBS_OUTPUT does not shows the ressult until the script finish, the wait time will make you run crazy. The “solution” is to use a log file and use UTL_FILE which has FLUSH function for instant feedback!

A sample code for this will be:

DECLARE
    Rows_count PLS_INTEGER := 0;
    LOGFILE UTL_FILE.FILE_TYPE;
BEGIN
    LOGFILE := UTL_FILE.FOPEN('LOGDIR','testoutput.log','w',1024);
    WHILE TRUE LOOP
        Rows_count := Rows_count + 1;
        IF MOD(Rows_count, 10000) = 0 THEN
            dbms_output.put_line('Commited ' || Rows_count || ' rows');
            UTL_FILE.PUT_LINE(LOGFILE,'Commited ' || Rows_count || ' rows');
            UTL_FILE.FFLUSH (LOGFILE);
        END IF;
    END LOOP;
    COMMIT;
    UTL_FILE.FCLOSE(LOGFILE);
END;
/