79 select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES) from USER_EXTENTS |
79 select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES) from USER_EXTENTS |
80 group by SEGMENT_NAME, SEGMENT_TYPE order by sum(BYTES); |
80 group by SEGMENT_NAME, SEGMENT_TYPE order by sum(BYTES); |
81 |
81 |
82 select sum(BYTES) from USER_EXTENTS; |
82 select sum(BYTES) from USER_EXTENTS; |
83 |
83 |
84 Tables indexes:: |
84 Table indexes restricted to user:: |
85 |
85 |
86 select * from USER_INDEXES order by TABLE_NAME; |
86 select * from USER_INDEXES order by TABLE_NAME; |
|
87 |
|
88 Table indexes available to user:: |
|
89 |
|
90 select * from ALL_INDEXES order by TABLE_NAME; |
|
91 |
|
92 All table indexes:: |
|
93 |
|
94 select * from DBA_INDEXES order by TABLE_NAME; |
|
95 |
|
96 View index columns:: |
|
97 |
|
98 select * from DBA_IND_COLUMNS; |
|
99 select * from ALL_IND_COLUMNS; |
|
100 select * from USER_IND_COLUMNS; |
|
101 |
|
102 Vie index expressions:: |
|
103 |
|
104 select * from DBA_IND_EXPRESSIONS; |
|
105 select * from ALL_IND_EXPRESSIONS; |
|
106 select * from USER_IND_EXPRESSIONS; |
87 |
107 |
88 List of index sizes:: |
108 List of index sizes:: |
89 |
109 |
90 select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes |
110 select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes |
91 left outer join user_extents on user_extents.segment_name = table_name |
111 left outer join user_extents on user_extents.segment_name = table_name |
92 group by index_name, table_name |
112 group by index_name, table_name |
93 order by table_name; |
113 order by table_name; |
|
114 |
|
115 View index statistics:: |
|
116 |
|
117 select * from DBA_IND_STATISTICS; |
|
118 select * from ALL_IND_STATISTICS; |
|
119 select * from USER_IND_STATISTICS; |
|
120 select * from INDEX_STATS; |
94 |
121 |
95 List of tables without primary keys:: |
122 List of tables without primary keys:: |
96 |
123 |
97 select OWNER || '.' || TABLE_NAME from ALL_TABLES |
124 select OWNER || '.' || TABLE_NAME from ALL_TABLES |
98 where TABLE_NAME not in ( |
125 where TABLE_NAME not in ( |
99 select distinct TABLE_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P' |
126 select distinct TABLE_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P' |
100 ) and OWNER in ('USER1', 'USER2') |
127 ) and OWNER in ('USER1', 'USER2') |
101 order by OWNER, TABLE_NAME; |
128 order by OWNER, TABLE_NAME; |
102 |
129 |
103 List of currenct user constraints:: |
130 List of current constraints limited to current user:: |
104 |
131 |
105 select * from USER_CONSTRAINTS; |
132 select * from USER_CONSTRAINTS; |
|
133 |
|
134 List of constraints available to user:: |
|
135 |
|
136 select * from ALL_CONSTRAINTS; |
|
137 |
|
138 List of all constraints:: |
|
139 |
|
140 select * from DBA_CONSTRAINTS; |
|
141 |
|
142 .. note:: |
|
143 ``CONSTRAINT_TYPE``: |
|
144 |
|
145 * ``C`` (check constraint on a table) |
|
146 * ``P`` (primary key) |
|
147 * ``U`` (unique key) |
|
148 * ``R`` (referential integrity) |
|
149 * ``V`` (with check option, on a view) |
|
150 * ``O`` (with read only, on a view) |
106 |
151 |
107 List of tablespaces:: |
152 List of tablespaces:: |
108 |
153 |
109 select distinct TABLESPACE_NAME from USER_TABLES; |
154 select distinct TABLESPACE_NAME from USER_TABLES; |
110 |
155 |