Discover indexes and constraints.
--- a/oracle.rst Sat Sep 30 17:17:31 2017 +0300
+++ b/oracle.rst Wed Oct 04 17:40:29 2017 +0300
@@ -81,10 +81,30 @@
select sum(BYTES) from USER_EXTENTS;
-Tables indexes::
+Table indexes restricted to user::
select * from USER_INDEXES order by TABLE_NAME;
+Table indexes available to user::
+
+ select * from ALL_INDEXES order by TABLE_NAME;
+
+All table indexes::
+
+ select * from DBA_INDEXES order by TABLE_NAME;
+
+View index columns::
+
+ select * from DBA_IND_COLUMNS;
+ select * from ALL_IND_COLUMNS;
+ select * from USER_IND_COLUMNS;
+
+Vie index expressions::
+
+ select * from DBA_IND_EXPRESSIONS;
+ select * from ALL_IND_EXPRESSIONS;
+ select * from USER_IND_EXPRESSIONS;
+
List of index sizes::
select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes
@@ -92,6 +112,13 @@
group by index_name, table_name
order by table_name;
+View index statistics::
+
+ select * from DBA_IND_STATISTICS;
+ select * from ALL_IND_STATISTICS;
+ select * from USER_IND_STATISTICS;
+ select * from INDEX_STATS;
+
List of tables without primary keys::
select OWNER || '.' || TABLE_NAME from ALL_TABLES
@@ -100,10 +127,28 @@
) and OWNER in ('USER1', 'USER2')
order by OWNER, TABLE_NAME;
-List of currenct user constraints::
+List of current constraints limited to current user::
select * from USER_CONSTRAINTS;
+List of constraints available to user::
+
+ select * from ALL_CONSTRAINTS;
+
+List of all constraints::
+
+ select * from DBA_CONSTRAINTS;
+
+.. note::
+ ``CONSTRAINT_TYPE``:
+
+ * ``C`` (check constraint on a table)
+ * ``P`` (primary key)
+ * ``U`` (unique key)
+ * ``R`` (referential integrity)
+ * ``V`` (with check option, on a view)
+ * ``O`` (with read only, on a view)
+
List of tablespaces::
select distinct TABLESPACE_NAME from USER_TABLES;