1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
set line 199
set long 100000
set pages 1000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_users WHERE username = '&&Username') > 0)
THEN dbms_metadata.get_ddl ('USER', '&&Username')
ELSE to_clob (' -- Note: User not found!')
END ) extracted_ddl
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_ts_quotas WHERE username = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
ELSE to_clob (' -- Note: No TS Quotas found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_role_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
ELSE to_clob (' -- Note: No granted Roles found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_sys_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
ELSE to_clob (' -- Note: No System Privileges found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_tab_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
ELSE to_clob (' -- Note: No Object Privileges found!')
END )
FROM dual
define owner=BZHIS
define table_name=ZYGL_DXSF
set linesize 160
col DATA_TYPE for a15
set pagesize 10000
col COLUMN_NAME for a30
col col for a30
select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tables t where owner = upper('&owner') and table_name = upper('&table_name');
select COLUMN_NAME, DATA_TYPE, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_columns t where owner = upper('&owner') and table_name = upper('&table_name');
select INDEX_NAME, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_indexes t where table_name = upper('&table_name') and table_owner = upper('&owner');
select i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from dba_ind_columns i, dba_tab_columns t where i.index_owner=t.owner and i.table_name = upper('&table_name') and i.index_owner = upper('&owner') and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position;
select col.table_owner "table_owner",
idx.table_name "table_name",
col.index_owner "index_owner",
idx.index_name "index_name",
uniqueness "uniqueness",
status,
column_name "column_name",
column_position
from dba_ind_columns col, dba_indexes idx
where col.index_name = idx.index_name
and col.table_name = idx.table_name and col.table_owner = idx.table_owner
and col.table_owner='&owner'
and col.table_name='&table_name')
order by idx.table_type,
idx.table_name,
idx.index_name,
col.table_owner,
column_position;
exec dbms_stats.gather_table_stats('STEVEN','AWEN_OGG_TEST',degree=>10,cascade=> TRUE,no_invalidate=>false);
|