set echo off
set heading off
set feedback off
set linesize 512
set pagesize 10000

prompt ------------------------------------------------------
prompt - Compte les lignes de chaque table du user courant --
prompt ------------------------------------------------------

SET TERM OFF 

spool compte_lignes_tables.tmp

SELECT 
   'select '''||table_name||' = ''||count(*) from '
   ||  table_name||' having count(*) > 0;'
FROM 
   user_tables 
WHERE 
   table_name not like 'SYS_IOT_OVER_%' 
ORDER BY 
   table_name;

spool off

SET TERM ON 

@compte_lignes_tables.tmp

set echo off
set feedback off
set linesize 512

prompt -------------------------------
prompt - Liste des tables de la base -
prompt -------------------------------

break on OWNER

SELECT
   *
FROM 
   DBA_TABLES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   OWNER, 
   TABLE_NAME;

set echo off
set feedback off
set linesize 512

prompt -------------------------------
prompt - Liste des tables sans index -
prompt -------------------------------

break on OWNER skip 1

SELECT
   OWNER, 
   TABLE_NAME
FROM 
   ALL_TABLES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
MINUS
SELECT 
   OWNER, 
   TABLE_NAME
FROM 
   ALL_INDEXES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP');

set echo off
set feedback off
set linesize 512

prompt -----------------------------------
prompt - Liste des tables très indexées --
prompt -----------------------------------

SELECT
   OWNER, 
   TABLE_NAME, 
   COUNT (*) "Nbre"
FROM 
   ALL_INDEXES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
GROUP BY 
   OWNER, 
   TABLE_NAME
HAVING 
   COUNT (*) > ('6');

set echo off
set feedback off
set linesize 512

prompt ---------------------------------------------
prompt - Liste des tables partitionnées de la base -
prompt ---------------------------------------------

break on TABLE_OWNER on TABLE_NAME skip 1

SELECT
   TABLE_OWNER, 
   TABLE_NAME, 
   TABLESPACE_NAME,
   PARTITION_NAME, 
   PARTITION_POSITION,
   LOGGING, 
   HIGH_VALUE, 
   PCT_FREE,
   PCT_USED, 
   INI_TRANS, 
   MAX_TRANS,
   INITIAL_EXTENT, 
   NEXT_EXTENT,
   MAX_EXTENT, 
   PCT_INCREASE
FROM 
   SYS.DBA_TAB_PARTITIONS
WHERE
   TABLE_OWNER NOT IN ('SYS','SYSTEM')
ORDER BY 
   TABLE_OWNER, 
   TABLE_NAME, 
   PARTITION_POSITION;

set echo off
set heading off
set feedback off
set linesize 130

prompt -------------------------------------------------
prompt - Désactive les clés primaires du user connecté -
prompt -------------------------------------------------

set term off
spool pk_desactivees.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter table '||owner||'.'||table_name
   ||' disable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
   constraint_type = 'R'
AND 
   status = 'ENABLED';

SELECT 
   'alter table '||owner||'.'||table_name
   ||' disable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
   constraint_type = 'P'
AND 
   status = 'ENABLED';

spool off

set term on

@pk_desactivees.tmp

set echo off
set heading off
set feedback off
set linesize 130

prompt ----------------------------------------------
prompt - Active les clés primaires du user connecté -
prompt ----------------------------------------------

set term off
spool pk_activees.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter table '||owner||'.'||table_name
   ||' enable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
  constraint_type = 'P'
AND 
  status != 'ENABLED';

SELECT 
   'alter table '||owner||'.'||table_name
   ||' enable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
   constraint_type = 'R'
AND 
   status != 'ENABLED';

spool off
set term on

@pk_activees.tmp

set echo off
set heading off
set feedback off
set linesize 150

prompt -----------------------------------------------------------------
prompt - Désactive toutes les contraintes FK de l'utilisateur connecté -
prompt -----------------------------------------------------------------

set term off
spool fk_desactivees.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter table '||owner||'.'||table_name
   ||' disable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
   constraint_type = 'R'
AND 
   status = 'ENABLED';

spool off
set term on

@fk_desactivees.tmp

set echo off
set heading off
set feedback off
set linesize 150

prompt --------------------------------------------------------------
prompt - Active toutes les contraintes FK de l'utilisateur connecté -
prompt --------------------------------------------------------------

set term off
spool fk_activees.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter table '||owner||'.'||table_name
   ||' enable constraint '||constraint_name||';'
FROM 
   user_constraints
WHERE 
   constraint_type = 'R'
AND 
   status != 'ENABLED';

spool off
set term on

@fk_activees.tmp

set echo off
set feedback off
set linesize 512

prompt -----------------------------------------------
prompt - liste des tables Advanced queues de la base -
prompt -----------------------------------------------

SELECT
   *
FROM 
   DBA_QUEUE_TABLES
ORDER BY 
   OWNER, 
   QUEUE_TABLE;

prompt ------------------------------------------
prompt - Liste des  Advanced Queues de la base --
prompt ------------------------------------------

SELECT 
   OWNER, 
   QUEUE_TABLE, 
   NAME, QID, 
   QUEUE_TYPE, 
   MAX_RETRIES, 
   RETRY_DELAY, 
   ENQUEUE_ENABLED, 
   DEQUEUE_ENABLED, 
   RETENTION, 
   USER_COMMENT
FROM 
   DBA_QUEUES
ORDER BY 
   OWNER, 
   QUEUE_TABLE, 
   NAME;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Liste des clusters de la base --
prompt ----------------------------------

column tab_column_name format a32
break on OWNER skip 1 on TABLESPACE_NAME on CLUSTER_NAME on TABLE_NAME

SELECT 
   A.OWNER, 
   A.TABLESPACE_NAME, 
   A.CLUSTER_NAME, 
   B.TABLE_NAME,
   B.TAB_COLUMN_NAME, 
   B.CLU_COLUMN_NAME, 
   A.CLUSTER_TYPE,
   A.AVG_BLOCKS_PER_KEY, 
   A.KEY_SIZE, 
   A.FUNCTION, 
   A.HASHKEYS, 
   A.PCT_FREE,
   A.PCT_USED, 
   A.INI_TRANS, 
   A.MAX_TRANS, 
   A.INITIAL_EXTENT, 
   A.NEXT_EXTENT,
   A.MIN_EXTENTS, 
   A.MAX_EXTENTS, 
   A.PCT_INCREASE
FROM 
   DBA_CLUSTERS A, 
   DBA_CLU_COLUMNS B
WHERE 
   A.OWNER = B.OWNER
AND 
   A.CLUSTER_NAME = B.CLUSTER_NAME
AND 
   A.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   1, 
   2, 
   3, 
   4;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Liste des contexts de la base --
prompt ----------------------------------

SELECT 
   NAMESPACE, 
   SCHEMA, 
   PACKAGE
FROM 
   DBA_CONTEXT
ORDER BY 
   NAMESPACE;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------
prompt - Liste des DB Links de la base -
prompt ---------------------------------

column host format a32
column db_link format a32

break on OWNER skip 1

SELECT
   A.OWNER, 
   A.HOST, 
   A.DB_LINK, 
   A.USERNAME, 
   A.CREATED,
   DECODE (B.FLAG, 0, 'NO', 1, 'YES') "DEC", B.AUTHUSR, C.STATUS
FROM 
   DBA_DB_LINKS A, 
   SYS.USER$ U, 
   SYS.LINK$ B, 
   DBA_OBJECTS C
WHERE 
   A.DB_LINK = B.NAME 
AND
   A.OWNER   = U.NAME 
AND
   B.OWNER#  = U.USER# 
AND
   A.DB_LINK = C.OBJECT_NAME 
AND
   A.OWNER   = C.OWNER 
AND
   C.OBJECT_TYPE = 'DATABASE LINK'
ORDER BY 
   1, 
   2, 
   3;

set echo off
set feedback off
set linesize 512

prompt -----------------------------------
prompt - Liste des Dimensions de la base -
prompt -----------------------------------

break on OWNER

SELECT 
   OWNER, 
   DIMENSION_NAME, 
   DECODE (INVALID, 'Y', 'YES', 'N', 'NO') "DEC", REVISION
FROM 
   DBA_DIMENSIONS
ORDER BY 
   OWNER, 
   DIMENSION_NAME;

set echo off
set feedback off
set linesize 512

prompt ------------------------------------------
prompt - Liste des clés étrangères non indexées -
prompt ------------------------------------------

column column_name format a32
break on OWNER skip 1 on TABLE_NAME

SELECT
   ACC.OWNER, 
   ACC.TABLE_NAME, 
   ACC.CONSTRAINT_NAME, 
   ACC.COLUMN_NAME,
   ACC.POSITION
FROM 
   ALL_CONS_COLUMNS ACC, 
   ALL_CONSTRAINTS AC
WHERE 
   ACC.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   AC.TABLE_NAME = ACC.TABLE_NAME
AND 
   ACC.OWNER = AC.OWNER
AND 
   AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND 
   AC.CONSTRAINT_TYPE = 'R'
AND 
   (ACC.OWNER, ACC.TABLE_NAME, ACC.COLUMN_NAME, ACC.POSITION) IN
   ( SELECT 
        ACC.OWNER, 
        ACC.TABLE_NAME,
        ACC.COLUMN_NAME,
        ACC.POSITION
     FROM 
        ALL_CONS_COLUMNS ACC, 
        ALL_CONSTRAINTS AC
     WHERE 
        AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
     AND 
        AC.CONSTRAINT_TYPE = 'R'
     MINUS
     SELECT 
        TABLE_OWNER, 
        TABLE_NAME, 
        COLUMN_NAME, 
        COLUMN_POSITION
     FROM 
        ALL_IND_COLUMNS
   )
ORDER BY 
   ACC.OWNER, 
   ACC.CONSTRAINT_NAME, 
   ACC.COLUMN_NAME, 
   ACC.POSITION;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------------------------
prompt - Liste des clés étrangères de l'utilisateur connecté -
prompt -------------------------------------------------------

SELECT 
   table_name, 
   constraint_name, 
   r_owner, 
   r_constraint_name, 
   delete_rule, 
   status
FROM 
   user_constraints
WHERE 
   constraint_type = 'R'
ORDER BY 
   table_name, 
   constraint_name;

set echo off
set feedback off
set linesize 512

prompt ------------------------------
prompt - Liste des index de la base -
prompt ------------------------------

break on OWNER skip 1

SELECT
   *
FROM 
   DBA_INDEXES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   OWNER, 
   TABLE_OWNER, 
   TABLE_NAME;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------------
prompt - Liste des colonnes indexées de la base --
prompt -------------------------------------------

column COLUMN_NAME format a32

break on INDEX_OWNER
break on TABLE_OWNER
break on TABLE_NAME
break on INDEX_NAME
break on INDEX_TYPE
break on UNIQUENESS

SELECT
   A.OWNER INDEX_OWNER, 
   A.TABLE_OWNER, 
   A.TABLE_NAME, 
   A.INDEX_NAME,
   A.INDEX_TYPE, 
   A.UNIQUENESS, 
   B.COLUMN_POSITION, 
   B.COLUMN_NAME
FROM 
   DBA_INDEXES A, 
   DBA_IND_COLUMNS B, 
   DBA_TABLES C
WHERE 
   A.OWNER = B.INDEX_OWNER
AND 
   A.OWNER = C.OWNER
AND 
   A.TABLE_NAME = B.TABLE_NAME
AND 
   A.TABLE_NAME = C.TABLE_NAME
AND 
   A.INDEX_NAME = B.INDEX_NAME
AND 
   A.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   A.TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   A.OWNER, 
   A.TABLE_OWNER, 
   A.TABLE_NAME, 
   A.INDEX_NAME, 
   B.COLUMN_POSITION;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------------
prompt - Liste des index partitionnés de la base -
prompt -------------------------------------------

break on INDEX_OWNER skip 1 on TABLE_NAME on INDEX_NAME

SELECT
   IP.INDEX_OWNER, 
   IX.TABLE_OWNER || '.' || IX.TABLE_NAME "OWNER.TABLE", 
   IP.INDEX_NAME, 
   IP.PARTITION_NAME,
   IP.PARTITION_POSITION, 
   IP.BLEVEL, 
   IP.CLUSTERING_FACTOR, 
   IP.DISTINCT_KEYS,
   IP.NUM_ROWS, 
   IP.PCT_FREE, 
   IP.INI_TRANS, 
   IP.MAX_TRANS, 
   IP.INITIAL_EXTENT,
   IP.NEXT_EXTENT, 
   IP.MIN_EXTENT, 
   IP.MAX_EXTENT, 
   IP.PCT_INCREASE, 
   IP.STATUS,
   IP.LEAF_BLOCKS, 
   IP.AVG_LEAF_BLOCKS_PER_KEY, 
   IP.AVG_DATA_BLOCKS_PER_KEY,
   IP.SAMPLE_SIZE, 
   IP.LAST_ANALYZED
FROM 
   DBA_INDEXES IX,
   DBA_IND_PARTITIONS IP
WHERE 
   IX.OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   IX.TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   IX.PARTITIONED = 'YES'
AND 
   IP.INDEX_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   IP.INDEX_OWNER = IX.OWNER
AND 
   IP.INDEX_NAME = IX.INDEX_NAME  
ORDER BY 
   1, 
   2, 
   3;

set echo off
set feedback off
set linesize 512

prompt -------------------------------
prompt - Liste des index similaires --
prompt -------------------------------

column column_name format a32
column table_name format a32
column index_name format a32
column table_owner format a32

break on TABLE_OWNER on TABLE NAME skip 1

SELECT
   TABLE_OWNER, 
   TABLE_NAME, 
   INDEX_NAME, 
   COLUMN_NAME
FROM 
   ALL_IND_COLUMNS
WHERE 
   COLUMN_POSITION = 1
AND 
   TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND 
   (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN
   (
      SELECT 
         TABLE_OWNER, 
         TABLE_NAME, 
         COLUMN_NAME
      FROM 
         (
           SELECT
              TABLE_OWNER, 
              TABLE_NAME, 
              COLUMN_NAME,
              COUNT (*) TCOUNT
           FROM 
              ALL_IND_COLUMNS
           WHERE 
              COLUMN_POSITION = 1
           AND 
              TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
           HAVING 
              COUNT (*) > 1
           GROUP BY 
              TABLE_OWNER, 
              TABLE_NAME, 
              COLUMN_NAME
          )
   )
ORDER BY 
   TABLE_OWNER, 
   TABLE_NAME, 
   COLUMN_NAME, 
   INDEX_NAME;

set echo off
set feedback off
set linesize 512

prompt -----------------------------
prompt - Liste des jobs programmés -
prompt -----------------------------

column interval format a40
column what format a40

SELECT
   JOB, 
   LOG_USER, 
   PRIV_USER, 
   SCHEMA_USER, 
   LAST_DATE, 
   THIS_DATE, 
   NEXT_DATE, 
   TOTAL_TIME, 
   DECODE (BROKEN, 'Y', 'YES', 'N', 'NO') "JOB_BROKEN", 
   INTERVAL, 
   FAILURES, 
   TRANSLATE(WHAT,chr(10), ' ') WHAT
FROM 
   DBA_JOBS
ORDER BY 
   JOB;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Liste des Libraries de la base -
prompt ----------------------------------

column file_spec format a60

break on OWNER skip 1

SELECT
   OWNER, 
   LIBRARY_NAME, 
   FILE_SPEC, 
   DECODE (DYNAMIC, 'Y', 'YES', 'N', 'NO') "DEC",
   STATUS
FROM 
   DBA_LIBRARIES
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   OWNER, 
   LIBRARY_NAME;

set echo off
set feedback off
set linesize 512

prompt -----------------------------------
prompt - Liste des Operateurs de la base -
prompt -----------------------------------

SELECT 
   OWNER, 
   OPERATOR_NAME, 
   NUMBER_OF_BINDS
FROM 
   DBA_OPERATORS
ORDER BY 
   OWNER, 
   OPERATOR_NAME;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------
prompt - Liste des Outlines de la base -
prompt ---------------------------------

column version format a10

SELECT 
   OWNER, 
   NAME, 
   CATEGORY, 
   USED, 
   TIMESTAMP, 
   VERSION, 
   SQL_TEXT
FROM 
   DBA_OUTLINES
ORDER BY 
   OWNER, 
   NAME;

set echo off
set feedback off
set linesize 512

prompt --------------------------------------------
prompt - Liste des clés primaires du user conncté -
prompt --------------------------------------------

SELECT 
   table_name, 
   constraint_name, 
   constraint_type, 
   status
FROM 
   user_constraints
WHERE 
   constraint_type = 'P'
ORDER BY 
   table_name, 
   constraint_name;

set echo off
set feedback off
set linesize 512

prompt -----------------------------------
prompt - Liste des séquences de la base --
prompt -----------------------------------

column CYCLE format a8
column ORDRE format a8

break on SEQUENCE_OWNER skip 1

SELECT
   SEQUENCE_OWNER, 
   SEQUENCE_NAME, 
   MIN_VALUE, 
   MAX_VALUE, 
   INCREMENT_BY,
   DECODE (CYCLE_FLAG, 'Y', 'YES', 'N', 'NO') CYCLE,
   DECODE (ORDER_FLAG, 'Y', 'YES', 'N', 'NO') ORDRE, 
   CACHE_SIZE,
   LAST_NUMBER
FROM 
   DBA_SEQUENCES
WHERE 
   SEQUENCE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   1, 
   2;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Liste des Snapshots de la base -
prompt ----------------------------------

column snapshot format a62
column source format a62
column master_link format a32


SELECT
   OWNER,
   NAME
|| '.'
|| TABLE_NAME "SNAPSHOT",
   MASTER_VIEW,    
   MASTER_OWNER
|| '.'
|| MASTER "SOURCE",
   MASTER_LINK, 
   CAN_USE_LOG,
   UPDATABLE, 
   LAST_REFRESH,
   REFRESH_GROUP, 
   TYPE,
   UPDATE_TRIG, 
   UPDATE_LOG, 
   ERROR,
   MASTER_ROLLBACK_SEG, 
   QUERY
FROM 
   DBA_SNAPSHOTS
ORDER BY 
   1, 
   3, 
   5;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Liste Snapshot Logs de la base -
prompt ----------------------------------

break on LOG_OWNER skip 1

SELECT
   LOG_OWNER, 
   MASTER, 
   LOG_TABLE, 
   LOG_TRIGGER, 
   ROWIDS, 
   PRIMARY_KEY,
   FILTER_COLUMNS, 
   CURRENT_SNAPSHOTS
FROM 
   DBA_SNAPSHOT_LOGS
ORDER BY 
   1, 
   2;

set echo off
set feedback off
set linesize 512

prompt ------------------------------------
prompt - Liste des types objet de la base -
prompt ------------------------------------

break on OWNER on TYPE_NAME

SELECT
   OWNER, 
   TYPE_NAME, 
   ATTR_NAME, 
   ATTR_NO, 
   ATTR_TYPE_NAME, 
   LENGTH,
   PRECISION, 
   SCALE
FROM 
   DBA_TYPE_ATTRS
WHERE 
   OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
ORDER BY 
   OWNER, 
   TYPE_NAME, 
   ATTR_NO;