Здравствуйте, Аноним, Вы писали:
А>А если будет не 2 а 5 таблиц такое можно сделать??
Да собственно нет особой разницы
SQL> with
2 tables_list as (
3 select owner, view_name table_name from dba_views
4 where view_name in ( 'MVIEW_WORKLOAD', 'USER_TAB_COLUMNS', 'KU$_COLUMN_VIEW' )),
5 number_columns as (
6 select tc.owner, tc.table_name, column_name,
7 row_number() over (partition by tc.table_name order by column_name) col_number
8 from dba_tab_columns tc, tables_list tl
9 where data_type = 'NUMBER' and tc.owner = tl.owner and tc.table_name = tl.table_name ),
10 string_columns as (
11 select tc.owner, tc.table_name, column_name,
12 row_number() over (partition by tc.table_name order by column_name) col_number
13 from dba_tab_columns tc, tables_list tl
14 where data_type = 'VARCHAR2' and tc.owner = tl.owner and tc.table_name = tl.table_name ),
15 date_columns as (
16 select tc.owner, tc.table_name, column_name,
17 row_number() over (partition by tc.table_name order by column_name) col_number
18 from dba_tab_columns tc, tables_list tl
19 where data_type = 'DATE' and tc.owner = tl.owner and tc.table_name = tl.table_name),
20 other_columns as (
21 select tc.owner, tc.table_name, column_name, data_type,
22 row_number() over (partition by tc.table_name order by column_name) col_number
23 from dba_tab_columns tc, tables_list tl
24 where data_type not in ( 'DATE', 'NUMBER', 'VARCHAR2' ) and tc.owner = tl.owner and tc.table_name = tl.table_name )
25 select
26 case when length ( s."number fields:" || s."string fields:" || s."date fields:" || s."other fields:" ) > 0
27 then s."table:"
28 else '- - - - - - - -'
29 end "table:",
30 s."number fields:", s."string fields:", s."date fields:", s."other fields:"
31 from (
32 select
33 coalesce ( nc.table_name, sc.table_name, dc.table_name, oc.table_name ) "table:",
34 nc.column_name "number fields:",
35 sc.column_name "string fields:",
36 dc.column_name "date fields:",
37 case when oc.column_name is not null
38 then oc.column_name || ' (' || oc.data_type || ')'
39 else null
40 end "other fields:"
41 from
42 number_columns nc full outer join string_columns sc
43 on ( nc.table_name = sc.table_name and nc.owner = sc.owner and
44 nc.col_number = sc.col_number )
45 full outer join date_columns dc
46 on ( nc.table_name = dc.table_name and nc.owner = dc.owner and
47 nc.col_number = dc.col_number )
48 full outer join other_columns oc
49 on ( nc.table_name = oc.table_name and nc.owner = oc.owner and
50 nc.col_number = oc.col_number )
51 order by 1, 2, 3, 4, 5 ) s
52 group by grouping sets (
53 (s."table:", s."number fields:", s."string fields:", s."date fields:", s."other fields:"),
54 (s."table:"))
55 order by s."table:"
56 /
table: number fields: string fields: date fields: other fields:
-------------------- -------------------- -------------------- -------------------- -------------------------
KU$_COLUMN_VIEW CHARSETFORM ATTRNAME SPARE6 CON (KU$_CONSTRAINT0_T)
KU$_COLUMN_VIEW CHARSETID DEFAULT_VAL LOBMD (KU$_LOB_T)
KU$_COLUMN_VIEW COL_NUM NAME OIDINDEX (KU$_OIDINDEX_T)
KU$_COLUMN_VIEW DEFLENGTH SPARE4 OPQMD (KU$_OPQTYPE_T)
KU$_COLUMN_VIEW FIXEDSTORAGE SPARE5 TYPEMD (KU$_COLTYPE_T)
KU$_COLUMN_VIEW INTCOL_NUM
KU$_COLUMN_VIEW IS_NULL
KU$_COLUMN_VIEW LENGTH
KU$_COLUMN_VIEW OBJ_NUM
KU$_COLUMN_VIEW OFFSET
KU$_COLUMN_VIEW PRECISION_NUM
KU$_COLUMN_VIEW PROPERTY
KU$_COLUMN_VIEW SCALE
KU$_COLUMN_VIEW SEGCOLLENGTH
KU$_COLUMN_VIEW SEGCOL_NUM
KU$_COLUMN_VIEW SPARE1
KU$_COLUMN_VIEW SPARE2
KU$_COLUMN_VIEW SPARE3
KU$_COLUMN_VIEW TYPE_NUM
- - - - - - - -
MVIEW_WORKLOAD CARDINALITY APPLICATION IMPORT_TIME QUERY (LONG)
MVIEW_WORKLOAD FREQUENCY OWNER LASTUSE
MVIEW_WORKLOAD PRIORITY
MVIEW_WORKLOAD QUERYID
MVIEW_WORKLOAD RESPONSETIME
MVIEW_WORKLOAD RESULTSIZE
MVIEW_WORKLOAD WORKLOADID
- - - - - - - -
USER_TAB_COLUMNS AVG_COL_LEN CHARACTER_SET_NAME LAST_ANALYZED DATA_DEFAULT (LONG)
USER_TAB_COLUMNS CHAR_COL_DECL_LENGTH CHAR_USED HIGH_VALUE (RAW)
USER_TAB_COLUMNS CHAR_LENGTH COLUMN_NAME LOW_VALUE (RAW)
USER_TAB_COLUMNS COLUMN_ID DATA_TYPE
USER_TAB_COLUMNS DATA_LENGTH DATA_TYPE_MOD
USER_TAB_COLUMNS DATA_PRECISION DATA_TYPE_OWNER
USER_TAB_COLUMNS DATA_SCALE DATA_UPGRADED
USER_TAB_COLUMNS DEFAULT_LENGTH GLOBAL_STATS
USER_TAB_COLUMNS DENSITY NULLABLE
USER_TAB_COLUMNS NUM_BUCKETS TABLE_NAME
USER_TAB_COLUMNS NUM_DISTINCT USER_STATS
USER_TAB_COLUMNS NUM_NULLS V80_FMT_IMAGE
USER_TAB_COLUMNS SAMPLE_SIZE
- - - - - - - -
42 rows selected
