QUERYING CONSTRAINTS

SELECT co.table_name, co.column_name, co.position, c.constraint_name, 
            c.constraint_type, c.index_name, c.status, c.last_change
  FROM user_constraints c
 INNER JOIN user_cons_columns co 
    ON c.constraint_name = co.constraint_name AND c.owner = co.owner
 WHERE c.constraint_type = 'P' AND co.table_name= 'ANAG_AREE'
 ORDER BY co.table_name, co.position;

[It's possible to use <all_constraints, all_cons_columns> replacing 
 <user_constraints, user_cons_columns> to view all onwners]

C - Check constraint on a table (es: Not Nullable field) 
P - Primary key  
U - Unique key  
---------------------------------------
R - Referential integrity  (Foreign Key)
V - With check option, on a view  
O - With read only, on a view  
H - Hash expression  
F - Constraint that involves a REF column  
S - Supplemental logging

QUERYING INDEXES

SELECT co.table_name, co.index_name, co.column_name, co.column_position, 
       i.index_type, i.uniqueness, i.tablespace_name, i.last_analyzed
  FROM user_indexes i
 INNER JOIN user_ind_columns co
    ON i.table_name = co.TABLE_NAME AND i.index_name = co.INDEX_NAME
 WHERE i.table_name = 'ANAG_AREE'
 ORDER BY co.table_name, co.index_name;

QUERYING TABLES, VIEWS, STORED PROCEDURS

SELECT object_name, column_name, column_type, object_text, created, lastupdate, comments
FROM
-- [T]able, [V]iew
(SELECT CASE WHEN object_name IS NULL THEN 
           ''
        ELSE 
           '[' || SUBSTR(object_type, 0, 1) || '] ' || object_name 
        END object_name,
        tab.column_name, tab.column_type, tab.object_text, tab.created, 
        tab.lastupdate, tab.comments
  FROM (SELECT usr_com.table_name object_name,
               usr_com.table_type object_type,
               '' column_name,
               0 column_id,
               '' column_type,
               usr_com.comments,
               dbms_metadata.get_ddl(object_type,object_name,'SIGO') object_text,
               to_char(usr_obj.CREATED, 'MM/DD/YYYY HH24:MI:SS') created,
               to_char(usr_obj.LAST_DDL_TIME, 'MM/DD/YYYY HH24:MI:SS') lastupdate,
               usr_obj.OBJECT_TYPE || usr_com.table_name || 1 mtlevel
          FROM USER_TAB_COMMENTS usr_com
         INNER JOIN USER_OBJECTS usr_obj
            ON usr_com.TABLE_NAME = usr_obj.OBJECT_NAME
        UNION ALL
        SELECT '' object_name,
               '' object_type,
               usr_columns.column_name,
               usr_columns.column_id,
               CONCAT(CONCAT(CONCAT(usr_columns.data_type, '('), usr_columns.data_length),
                      ')') || ' [' || nullable || ']' column_type,
               usr_colcom.comments,
               NULL object_text,
               '' created,
               '' lastupdate,
               usr_obj.OBJECT_TYPE || usr_columns.table_name || 2 mtlevel
          FROM USER_TAB_COLUMNS usr_columns
         INNER JOIN USER_COL_COMMENTS usr_colcom
            ON usr_columns.COLUMN_NAME = usr_colcom.column_name
           AND usr_columns.TABLE_NAME = usr_colcom.table_name
         INNER JOIN USER_OBJECTS usr_obj on usr_columns.TABLE_NAME = usr_obj.OBJECT_NAME
           ) tab
 ORDER BY mtlevel, column_id)
 UNION ALL
 -- [P]rocedure, [S]equence, [F]unction
 (SELECT object_name, column_name, column_type, object_text, created, lastupdate, comments
  FROM (SELECT '[' || substr(object_type, 0, 1) || '] ' || object_name object_name,
               '' column_name,
               '' column_type,
               '' comments,
               dbms_metadata.get_ddl(DECODE(object_type, 'JOB', 'PROCOBJ', object_type), 
                                            object_name, 'SIGO') object_text,
               to_char(CREATED, 'MM/DD/YYYY HH24:MI:SS') created,
               to_char(LAST_DDL_TIME, 'MM/DD/YYYY HH24:MI:SS') lastupdate,
               object_type || object_name mtlevel
          FROM USER_OBJECTS
         WHERE OBJECT_TYPE IN ('PROCEDURE', 'SEQUENCE', 'FUNCTION', 'JOB')
         ORDER BY mtlevel))

-- tables source --
SELECT * FROM user_tab_columns WHERE table_name='UNES_LISTINI';
-- views source --
SELECT * FROM user_views WHERE view_name = 'V_OR_REFERENZE';
-- stored, functions source --
SELECT * FROM all_source WHERE name='SP_MARGINE_CLIENTI';
-- jobs --
SELECT * FROM USER_SCHEDULER_JOBS; SELECT * FROM user_scheduler_job_run_details;