62 lines
4.8 KiB
SQL
62 lines
4.8 KiB
SQL
SELECT c11204.owner
|
|
, c11204.table_name
|
|
, c11204.column_name
|
|
, CASE
|
|
WHEN c11204.data_type = 'NUMBER'
|
|
THEN c11204.data_type || NVL2(c11204.data_precision||c11204.data_scale, '(' || NVL(c11204.data_precision,'38') || ',' || NVL(c11204.data_scale,'127') || ')','')
|
|
WHEN c11204.data_type = 'FLOAT'
|
|
THEN c11204.data_type || NVL2(c11204.data_precision,'(' || c11204.data_precision || ')','')
|
|
WHEN c11204.data_type = 'VARCHAR2' OR c11204.data_type = 'CHAR'
|
|
THEN c11204.data_type || '(' || c11204.char_length || ' ' || DECODE(c11204.char_used,'B','BYTE','CHAR') || ')'
|
|
WHEN c11204.data_type = 'NVARCHAR2' OR c11204.data_type = 'NCHAR'
|
|
THEN c11204.data_type || '(' || c11204.char_length || ')'
|
|
WHEN c11204.data_type = 'RAW' OR c11204.data_type = 'UROWID'
|
|
THEN c11204.data_type || '(' || c11204.data_length || ')'
|
|
ELSE c11204.data_type
|
|
END DATA_TYPE
|
|
, NVL2(c11204.column_name,'Y',' ') "11204"
|
|
, NVL2(c11203.column_name,'Y',' ') "11203"
|
|
, NVL2(c11202.column_name,'Y',' ') "11202"
|
|
, NVL2(c11201.column_name,'Y',' ') "11201"
|
|
, NVL2(c11107.column_name,'Y',' ') "11107"
|
|
, NVL2(c11106.column_name,'Y',' ') "11106"
|
|
, NVL2(c10205.column_name,'Y',' ') "10205"
|
|
, NVL2(c10204.column_name,'Y',' ') "10204"
|
|
, NVL2(c10203.column_name,'Y',' ') "10203"
|
|
, NVL2(c10202.column_name,'Y',' ') "10202"
|
|
, NVL2(c10201.column_name,'Y',' ') "10201"
|
|
, NVL2(c10105.column_name,'Y',' ') "10105"
|
|
, NVL2(c10104.column_name,'Y',' ') "10104"
|
|
, NVL2(c10103.column_name,'Y',' ') "10103"
|
|
, NVL2(c9208.column_name,'Y',' ') "9208"
|
|
, NVL2(c9207.column_name,'Y',' ') "9207"
|
|
, NVL2(c9206.column_name,'Y',' ') "9206"
|
|
, NVL2(c9204.column_name,'Y',' ') "9204"
|
|
FROM dba_tab_columns c11204
|
|
LEFT OUTER JOIN dba_tab_columns@"9204" c9204 ON c11204.OWNER = c9204.OWNER AND c11204.TABLE_NAME = c9204.TABLE_NAME AND c11204.COLUMN_NAME = c9204.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"11203" c11203 ON c11204.OWNER = c11203.OWNER AND c11204.TABLE_NAME = c11203.TABLE_NAME AND c11204.COLUMN_NAME = c11203.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"11202" c11202 ON c11204.OWNER = c11202.OWNER AND c11204.TABLE_NAME = c11202.TABLE_NAME AND c11204.COLUMN_NAME = c11202.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"11201" c11201 ON c11204.OWNER = c11201.OWNER AND c11204.TABLE_NAME = c11201.TABLE_NAME AND c11204.COLUMN_NAME = c11201.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"11107" c11107 ON c11204.OWNER = c11107.OWNER AND c11204.TABLE_NAME = c11107.TABLE_NAME AND c11204.COLUMN_NAME = c11107.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"11106" c11106 ON c11204.OWNER = c11106.OWNER AND c11204.TABLE_NAME = c11106.TABLE_NAME AND c11204.COLUMN_NAME = c11106.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10205" c10205 ON c11204.OWNER = c10205.OWNER AND c11204.TABLE_NAME = c10205.TABLE_NAME AND c11204.COLUMN_NAME = c10205.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10204" c10204 ON c11204.OWNER = c10204.OWNER AND c11204.TABLE_NAME = c10204.TABLE_NAME AND c11204.COLUMN_NAME = c10204.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10203" c10203 ON c11204.OWNER = c10203.OWNER AND c11204.TABLE_NAME = c10203.TABLE_NAME AND c11204.COLUMN_NAME = c10203.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10202" c10202 ON c11204.OWNER = c10202.OWNER AND c11204.TABLE_NAME = c10202.TABLE_NAME AND c11204.COLUMN_NAME = c10202.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10201" c10201 ON c11204.OWNER = c10201.OWNER AND c11204.TABLE_NAME = c10201.TABLE_NAME AND c11204.COLUMN_NAME = c10201.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10105" c10105 ON c11204.OWNER = c10105.OWNER AND c11204.TABLE_NAME = c10105.TABLE_NAME AND c11204.COLUMN_NAME = c10105.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10104" c10104 ON c11204.OWNER = c10104.OWNER AND c11204.TABLE_NAME = c10104.TABLE_NAME AND c11204.COLUMN_NAME = c10104.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"10103" c10103 ON c11204.OWNER = c10103.OWNER AND c11204.TABLE_NAME = c10103.TABLE_NAME AND c11204.COLUMN_NAME = c10103.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"9208" c9208 ON c11204.OWNER = c9208.OWNER AND c11204.TABLE_NAME = c9208.TABLE_NAME AND c11204.COLUMN_NAME = c9208.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"9207" c9207 ON c11204.OWNER = c9208.OWNER AND c11204.TABLE_NAME = c9207.TABLE_NAME AND c11204.COLUMN_NAME = c9207.COLUMN_NAME
|
|
LEFT OUTER JOIN dba_tab_columns@"9206" c9206 ON c11204.OWNER = c9206.OWNER AND c11204.TABLE_NAME = c9206.TABLE_NAME AND c11204.COLUMN_NAME = c9206.COLUMN_NAME
|
|
WHERE c11204.owner = 'SYS'
|
|
AND c11204.table_name = 'V_$DATABASE'
|
|
-- AND c11204.column_name LIKE 'SUPP%'
|
|
ORDER BY c11204.owner
|
|
, c11204.table_name
|
|
-- , c11204.column_name
|
|
, c11204.column_id
|
|
;
|
|
|