forked from jeanron100/dbm_lite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathshowtab.sh
162 lines (145 loc) · 5.99 KB
/
showtab.sh
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
#!/bin/bash
TAB_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 40 feedback off verify off heading on echo off
col owner_name format a20
col table_name format a30
select owner,object_name table_name from dba_objects where object_name=upper('$2') and owner=upper('$1') and object_type='TABLE';
exit;
END`
if [ -z "$TAB_OWNER" ]; then
echo "no object exists, please check again"
exit 0
else
echo '*******************************************'
echo " $TAB_OWNER "
echo '*******************************************'
fi
sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
set serveroutput on
set pages 1000
set head on
set linesize 200
col table_name format a30
col partitoned format a10
col tablespace_name format a10
col status format a6
!echo '********** TABLE GENERAL INFO *****************';
select table_name,partitioned,tablespace_name,status,ini_trans,num_rows,blocks,empty_blocks,logging,monitoring,row_movement,last_analyzed from dba_tables where owner=upper('$1') and table_name=upper('$2');
!echo '********** TABLE STORAGE INFO *****************';
col initext format 99999999
col nxtext format 99999999
col minext format 999999999
col maxext format 9999999999
col compres format a10
select initial_extent initext,next_extent nxtext,min_extents minext,max_extents maxext,freelists,avg_space,chain_cnt,avg_row_len,cache,temporary,dependencies,compression compres from dba_tables where owner=upper('$1') and table_name=upper('$2');
!echo '********** TABLE columns INFO *****************';
col name format a20
set pages 1000
col table_name format a30
col column_name format a30
col data_type format a15
col nullable format a10
col data_default format a20
SELECT t1.column_id,
t1.COLUMN_NAME,
t1.DATA_TYPE
|| DECODE (
t1.DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || t1.DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || t1.DATA_LENGTH || ')')
AS DATA_TYPE,
t1.DATA_LENGTH,
t1.NULLABLE ,
t1.DATA_DEFAULT
FROM dba_TAB_COLUMNS t1
where table_name=upper('$2')
and owner=upper('$1')
order by t1.column_id;
!echo '********** CONSTRAINTS DETAILS INFO *****************';
col column_name format a20
col search_condition format a45
col constraint_name format a30
col r_owner format a10
col r_constraint_name format a15
col index_name format a30
select t2.column_name, t1.constraint_name,t1.constraint_type,t1.search_condition,t1.deferred,t1.deferrable,t1.rely,t1.index_name
from (select constraint_name,constraint_type,search_condition,deferred,deferrable,rely,index_name from dba_constraints
where table_name=upper('$2') and owner=upper('$1') ) t1,
(select constraint_name,column_name from dba_cons_columns where table_name=upper('$2') and owner=upper('$1') ) t2
where t1.constraint_name=t2.constraint_name
;
!echo '********** INDEX DETAILS INFO *****************';
col column_list format a30
col index_name format a30
col index_type format a10
col table_type format a10
select ind.index_name,ind.tablespace_name,ind.index_type,ind.uniqueness,ind.partitioned,temp_cols.column_list,
ind.table_type,ind.status,ind.num_rows,ind.last_analyzed,ind.generated from
(SELECT index_name, SUBSTR (MAX (SYS_CONNECT_BY_PATH (column_name, ',')), 2) column_list
FROM (SELECT /*+rule*/ index_name, column_name, rn, LEAD (rn) OVER (PARTITION BY index_name ORDER BY rn) rn1
FROM (SELECT index_name, column_name, ROW_NUMBER () OVER (ORDER BY column_position desc) rn
FROM dba_ind_columns where table_name=upper('$2') and table_owner=upper('$1')))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY index_name) temp_cols,dba_indexes ind
where ind.table_name=upper('$2') and ind.table_owner=upper('$1')
and ind.index_name=temp_cols.index_name
;
select t.table_name,i.index_name,
i.clustering_factor,t.blocks,t.num_rows
from dba_indexes i,dba_tables t
where i.table_name=t.table_name
and i.table_owner=t.owner
and t.table_name = upper('$2')
and t.owner=upper('$1')
order by t.table_name,i.index_name;
!echo '********** ROLE GRANTS DETAILS INFO *****************';
col grantor format a15
col privilege format a15
col object_name format a30
col role_name format a20
select grantor,privilege,table_name object_name,grantee role_name,grantable,hierarchy
from dba_tab_privs
where table_name=upper('$2') and owner=upper('$1')
and grantee in (select role from dba_roles )
;
!echo '********** OBJECT GRANTS DETAILS INFO *****************';
select grantor,privilege,table_name object_name,grantee role_name,grantable,hierarchy
from dba_tab_privs
where table_name=upper('$2') and owner=upper('$1')
and grantee in (select username from dba_users);
!echo '********** SYNONYMS DETAILS INFO *****************';
col db_link format a20
col owner format a20
col synonym_name format a30
col table_name format a30
col table_owner format a15
select owner,synonym_name,table_owner,table_name,db_link
from dba_synonyms
where table_name=upper('$2') and table_owner=upper('$1');
!echo '********** TRIGGER DETAIL INFO *****************';
col triggering_event format a20
col owner format a15
col trigger_name format a20
col trigger_type format a15
col base_object_type format a10
col status format a8
select owner,trigger_name,trigger_Type,triggering_event,base_object_Type,status,action_type
from dba_triggers where table_name=upper('$2') and owner=upper('$1');
EOF
exit