Oracle SQLS

schema.sql

-- master - slaver 主从流复制
-- 什么情况下才会对流复制造成影响
-- 主从数据不一致，或者从库上有的对象，主库又新建相同的对象

-- tables
SELECT * FROM user_tables

SELECT * FROM user_tab_comments WHERE table_name = upper(:name)

SELECT lower(t.column_name) column_name
    , c.comments
    , lower(t.data_type) data_type
    , t.data_length
    , t.data_precision
    , t.data_default
    , t.nullable
    , to_char(t.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
FROM user_tab_columns t, user_col_comments c
WHERE t.table_name = c.table_name
    AND t.column_name = c.column_name
    AND t.table_name = upper(:name)



-- sequences
SELECT * FROM user_sequences

SELECT * FROM user_sequences WHERE sequence_name = upper(:name)



-- procedures
SELECT object_name name
FROM user_procedures
WHERE object_type = 'PROCEDURE'
ORDER BY object_name

SELECT concat('#', concat(line|| '    ', text)) text
FROM user_source
WHERE type = 'PROCEDURE'
    AND name = upper(:name)
ORDER BY line



-- objects
-- name = ['trigger', 'proceduer', 'view', 'table']
SELECT * FROM user_objects WHERE object_type = upper(:name)



-- views
SELECT * FROM user_views



-- triggers
SELECT * FROM user_triggers

SELECT * FROM user_triggers_cols

SELECT trigger_name name
    , trigger_type type
    , triggering_event event
    , description \"desc\"
    , status
    , table_owner owner
FROM user_triggers
WHERE table_name = upper(:name)
SELECT concat('#', concat(line|| '    ', text)) text
    FROM user_source
WHERE type = 'TRIGGER'
    AND name = upper(:name)
ORDER BY line

SELECT concat('#', concat(line|| '    ', text)) text
FROM user_source
WHERE type = 'TRIGGER'
    AND name = upper(:name)
ORDER BY line


-- source
SELECT * FROM user_source



-- keys
SELECT con.constraint_name name
    , DECODE(con.constraint_type, 'P', 'primary', 'U', 'unique', 'R', 'foreign') type
    , lower(wm_concat(col.column_name)) columns
    , lower(con.status) status
    , wm_concat(col.position) position
    , con.owner
    , to_char(con.last_change, 'yyyy-mm-dd hh24:mi:ss') last_change
FROM user_constraints con, user_cons_columns col
WHERE con.constraint_name = col.constraint_name
    AND con.constraint_type IN ('P', 'R', 'U')
    AND col.table_name = upper(:name)
GROUP BY con.constraint_name
    , con.constraint_type
    , con.status
    , con.last_change
    , con.owner



-- indexes
SELECT u.index_name name
    , decode(i.uniqueness, 'UNIQUE', 'unique', 'normal') type
    , lower(wm_concat(u.column_name)) columns
    , lower(i.status) status
    , wm_concat(u.column_position) position
    , i.table_owner owner
    , to_char(i.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
FROM user_ind_columns u
    , user_indexes i
WHERE u.index_name = i.index_name
    AND u.table_name = i.table_name
    AND u.table_name = upper(:name)
GROUP BY u.index_name
    , i.uniqueness
    , i.table_owner
    , i.last_analyzed
    , i.status