Oracle 各情報取得

/* テーブル定義情報を取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,tbls.TABLE_NAME                   AS table_name
    ,comments.TABLE_TYPE               AS table_type
    ,comments.COMMENTS                 AS table_comment 
FROM
    USER_TABLES tbls
        LEFT OUTER JOIN USER_TAB_COMMENTS comments
        ON
            tbls.TABLE_NAME = comments.TABLE_NAME
ORDER BY
    tbls.TABLE_NAME;

/* カラム定義情報を取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,cols.TABLE_NAME                   AS table_name
    ,cols.COLUMN_NAME                  AS column_name
    ,cols.COLUMN_ID                    AS ordinal_position
    /* PKか否かの判別フラグ */
    ,(CASE 
        WHEN pk_cols.CONSTRAINT_NAME IS NULL THEN
            0
        ELSE
            1
     END)                              AS is_primary_key
    /* PK制約名 */
    ,pk_cols.CONSTRAINT_NAME           AS pk_constraint_name
    /* PK制約カラム位置 */
    ,pk_cols.KEY_ORDINAL               AS pk_key_ordinal
    /* UQか否かの判別フラグ */
    ,(CASE 
        WHEN uq_cols.CONSTRAINT_NAME IS NULL THEN
            0
        ELSE
            1
     END)                              AS is_unique_key
    /* UQ制約名 */
    ,uq_cols.CONSTRAINT_NAME           AS uq_constraint_name
    /* UQ制約カラム位置 */
    ,uq_cols.KEY_ORDINAL               AS uq_key_ordinal
    ,cols.DATA_TYPE                    AS column_data_type
    ,cols.DATA_DEFAULT                 AS column_default
    ,cols.NULLABLE                     AS is_nullable
    ,cols.DATA_LENGTH                  AS max_length
    ,cols.DATA_PRECISION               AS precision
    ,cols.DATA_SCALE                   AS scale
    ,comments.COMMENTS                 AS column_comment
FROM
    USER_TABLES tbls
        INNER JOIN USER_TAB_COLUMNS cols
        ON
            cols.TABLE_NAME = tbls.TABLE_NAME
        LEFT OUTER JOIN USER_COL_COMMENTS comments
        ON
                comments.TABLE_NAME  = cols.TABLE_NAME
            AND comments.COLUMN_NAME = cols.COLUMN_NAME
        /* PK情報を結合 */
        LEFT OUTER JOIN (
            SELECT
                 cons_inner.CONSTRAINT_NAME  AS constraint_name
                ,cons_columns_inner.POSITION AS key_ordinal
                ,tbls_inner.TABLE_NAME       AS table_name
                ,cols_inner.COLUMN_NAME      AS col_name
                ,cols_inner.COLUMN_ID        AS col_id
            FROM
                USER_TABLES tbls_inner
                    INNER JOIN USER_TAB_COLUMNS cols_inner
                    ON
                        cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME
                    INNER JOIN USER_CONSTRAINTS cons_inner
                    ON
                            cons_inner.OWNER           = USER
                        AND cons_inner.CONSTRAINT_TYPE = 'P'
                        AND cons_inner.TABLE_NAME      = tbls_inner.TABLE_NAME
                    INNER JOIN USER_CONS_COLUMNS cons_columns_inner
                    ON
                            cons_columns_inner.OWNER           = cons_inner.OWNER
                        AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME
                        AND cons_columns_inner.TABLE_NAME      = cons_inner.TABLE_NAME
                        AND cons_columns_inner.COLUMN_NAME     = cols_inner.COLUMN_NAME
        ) pk_cols
        ON
                pk_cols.TABLE_NAME = cols.TABLE_NAME
            AND pk_cols.COL_NAME   = cols.COLUMN_NAME
            AND pk_cols.COL_ID     = cols.COLUMN_ID 
        /* UQ情報を結合 */
        LEFT OUTER JOIN (
            SELECT
                 cons_inner.CONSTRAINT_NAME  AS constraint_name
                ,cons_columns_inner.POSITION AS key_ordinal
                ,tbls_inner.TABLE_NAME       AS table_name
                ,cols_inner.COLUMN_NAME      AS col_name
                ,cols_inner.COLUMN_ID        AS col_id
            FROM
                USER_TABLES tbls_inner
                    INNER JOIN USER_TAB_COLUMNS cols_inner
                    ON
                        cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME
                    INNER JOIN USER_CONSTRAINTS cons_inner
                    ON
                            cons_inner.OWNER           = USER
                        AND cons_inner.CONSTRAINT_TYPE = 'U'
                        AND cons_inner.TABLE_NAME      = tbls_inner.TABLE_NAME
                    INNER JOIN USER_CONS_COLUMNS cons_columns_inner
                    ON
                            cons_columns_inner.OWNER           = cons_inner.OWNER
                        AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME
                        AND cons_columns_inner.TABLE_NAME      = cons_inner.TABLE_NAME
                        AND cons_columns_inner.COLUMN_NAME     = cols_inner.COLUMN_NAME
        ) uq_cols
        ON
                uq_cols.TABLE_NAME = cols.TABLE_NAME
            AND uq_cols.COL_NAME   = cols.COLUMN_NAME
            AND uq_cols.COL_ID     = cols.COLUMN_ID
WHERE cols.COLUMN_NAME LIKE '%RETURN_ADDRESS%'
  AND USER = 'SCHG_INFO'
ORDER BY
    table_name, ordinal_position
;

/* ビュー定義を取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,views.VIEW_NAME                   AS view_name
    ,views.TEXT                        AS definition
FROM
    USER_VIEWS views
ORDER BY
    views.VIEW_NAME
;

/* トリガー定義の取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,triggers.TRIGGER_NAME             AS trigger_name
    ,source.LINE                       AS line
    ,source.TEXT                       AS definision
FROM
    USER_TRIGGERS triggers
        INNER JOIN USER_SOURCE source
        ON
                source.NAME = triggers.TRIGGER_NAME
            AND source.TYPE = 'TRIGGER'
ORDER BY
    trigger_name, line
;

/* シノニム情報を取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,synonyms.SYNONYM_NAME             AS synonym_name
    ,synonyms.TABLE_NAME               AS base_object_name
FROM
    USER_SYNONYMS synonyms
ORDER BY
    synonyms.SYNONYM_NAME
;

/* シーケンスの定義情報を取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,sequences.SEQUENCE_NAME           AS sequence_name
    ,sequences.MIN_VALUE               AS min_value
    ,sequences.MAX_VALUE               AS max_value
    ,sequences.INCREMENT_BY            AS increment_by
    ,sequences.CYCLE_FLAG              AS cycle_flag
    ,sequences.ORDER_FLAG              AS order_flag
    ,sequences.CACHE_SIZE              AS cache_size
    ,sequences.LAST_NUMBER             AS last_number
FROM
    USER_SEQUENCES sequences
ORDER BY
    sequences.SEQUENCE_NAME
;

/* プロシージャ定義の取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,procedures.PROCEDURE_NAME         AS procedure_name
    ,source.LINE                       AS line
    ,source.TEXT                       AS definision
FROM
    USER_PROCEDURES procedures
        INNER JOIN USER_SOURCE source
        ON
                source.NAME = procedures.PROCEDURE_NAME
            AND source.TYPE = 'PROCEDURE'
ORDER BY
    procedure_name, line
;

/* ファンクション定義の取得 */
SELECT
     SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
    ,USER                              AS schema_name
    ,procedures.PROCEDURE_NAME         AS procedure_name
    ,source.LINE                       AS line
    ,source.TEXT                       AS definision
FROM
    USER_PROCEDURES procedures
        INNER JOIN USER_SOURCE source
        ON
                source.NAME = procedures.PROCEDURE_NAME
            AND source.TYPE = 'FUNCTION'
ORDER BY
    procedure_name, line
;

/* ソース内容取得 */
select 
  CASE WHEN upper(TEXT) like '%SIS_ITEM_CODE%' THEN 'SIS_ITEM_CODE'
    WHEN upper(TEXT) like '%ITEM_KEY%' THEN 'ITEM_KEY'
    END AS SIS_CODE
  , US.*
FROM user_source US
WHERE 1 = 1
  AND TYPE IN ('PACKAGE BODY', 'PACKAGE', 'PROCEDURE','FUNCTION')
  AND UPPER(TEXT) LIKE '%xx%' --AND UPPER(TEXT) LIKE '%ITEM_NAME_JP%'
  and substr(trim(TEXT),1,2) <> '--'
ORDER BY name,line
;

SELECT *
FROM USER_SOURCE US
WHERE TYPE IN ('PACKAGE BODY', 'PACKAGE', 'PROCEDURE','FUNCTION')
  AND UPPER(TEXT) LIKE '%USERS_DB_WK%'
  AND SUBSTR(TRIM(TEXT),1,2) <> '--'
ORDER BY NAME,LINE
;

select dbms_metadata.get_ddl('PACKAGE','FN_EXPORT','SCHG_INFO_TEST') from dual
;

select DISTINCT TYPE
FROM user_source
;

--権限一覧
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'SCHG_INFO'
ORDER BY PRIVILEGE
;

/* sqlplus アクセス */
--connect to Oracle DB
>sqlplus [username]/[pw]@[hostname](e.g. //abc.com):1521/[SID]

--connect to Oracle DB and run sql script
>sqlplus [username]/[pw]@[hostname](e.g. //abc.com):1521/[SID] @"path/a.sql"

--connect to Oracle DB and run sql script and then exit
>sqlplus [username]/[pw]@[hostname](e.g. //abc.com):1521/[SID] @"path/a.sql"

--a.sql---------------------------------
set term off
set feed off

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 32767
--set numwidth 5

column tm new_value x noprint
select to_char(sysdate, 'YYYYMMDDhhmmss') tm from dual;

--prompt &x

spool d:\result1_&x..csv

--sql body
select item1 from tbl1;

spool off

--exit
----------------------------------------