I installed sample Oracle database schemas with the following steps:
Cloned the repository, changed the directories in the installation script, and started my docker container with the mapped folder:
sudo su - oracle
mkdir repos
cd repos
git clone https://github.com/oracle/db-sample-schemas.git
cd db-sample-schemas
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
exit
sudo docker run -d -it --rm --name oracle18se -v /home/oracle/oradata18:/opt/oracle/oradata -v /home/oracle/repos:/home/oracle/repos -p 1521:1521 -p 5500:5500 oracle/database-se:18.3.0
sudo docker logs oracle18se
Then I started the installation script as follows:
sudo docker exec -it oracle18se /bin/bash
cd ~/repos/db-sample-schemas/
ll mksample.sql
$ORACLE_HOME/bin/sqlplus SYSTEM/XpasswordX@\"localhost:1521/ORCLPDB1\"
@mksample XpasswordX XpasswordX hrpw oepw pmpw ixpw shpw bipw users temp /home/oracle/log localhost:1521/ORCLPDB1
where XpasswordX is the password generated while installing Oracle Instance. ‘users’ and ‘temp’ are table spaces that exist in a newly created database.
The installation script printed the following:
All named objects and stati
OWNER OBJECT_TYPE OBJECT_NAME SUBOBJECT_NAME STATUS
------ -------------------- ------------------------------ ---------------- --------
BI SYNONYM CHANNELS VALID
BI SYNONYM COSTS VALID
BI SYNONYM COUNTRIES VALID
BI SYNONYM CUSTOMERS VALID
BI SYNONYM PRODUCTS VALID
BI SYNONYM PROMOTIONS VALID
BI SYNONYM SALES VALID
BI SYNONYM TIMES VALID
HR INDEX COUNTRY_C_ID_PK VALID
HR INDEX DEPT_ID_PK VALID
HR INDEX DEPT_LOCATION_IX VALID
HR INDEX EMP_DEPARTMENT_IX VALID
HR INDEX EMP_EMAIL_UK VALID
HR INDEX EMP_EMP_ID_PK VALID
HR INDEX EMP_JOB_IX VALID
HR INDEX EMP_MANAGER_IX VALID
HR INDEX EMP_NAME_IX VALID
HR INDEX JHIST_DEPARTMENT_IX VALID
HR INDEX JHIST_EMPLOYEE_IX VALID
HR INDEX JHIST_EMP_ID_ST_DATE_PK VALID
HR INDEX JHIST_JOB_IX VALID
HR INDEX JOB_ID_PK VALID
HR INDEX LOC_CITY_IX VALID
HR INDEX LOC_COUNTRY_IX VALID
HR INDEX LOC_ID_PK VALID
HR INDEX LOC_STATE_PROVINCE_IX VALID
HR INDEX REG_ID_PK VALID
HR PROCEDURE ADD_JOB_HISTORY VALID
HR PROCEDURE SECURE_DML VALID
HR SEQUENCE DEPARTMENTS_SEQ VALID
HR SEQUENCE EMPLOYEES_SEQ VALID
HR SEQUENCE LOCATIONS_SEQ VALID
HR TABLE COUNTRIES VALID
HR TABLE DEPARTMENTS VALID
HR TABLE EMPLOYEES VALID
HR TABLE JOBS VALID
HR TABLE JOB_HISTORY VALID
HR TABLE LOCATIONS VALID
HR TABLE REGIONS VALID
HR TRIGGER SECURE_EMPLOYEES VALID
HR TRIGGER UPDATE_JOB_HISTORY VALID
HR VIEW EMP_DETAILS_VIEW VALID
IX EVALUATION CONTEXT AQ$_ORDERS_QUEUETABLE_V VALID
IX EVALUATION CONTEXT AQ$_STREAMS_QUEUE_TABLE_V VALID
IX INDEX AQ$_STREAMS_QUEUE_TABLE_Y VALID
IX QUEUE AQ$_ORDERS_QUEUETABLE_E VALID
IX QUEUE AQ$_STREAMS_QUEUE_TABLE_E VALID
IX QUEUE ORDERS_QUEUE VALID
IX QUEUE STREAMS_QUEUE VALID
IX RULE SET ORDERS_QUEUE_N VALID
IX RULE SET ORDERS_QUEUE_R VALID
IX RULE SET STREAMS_QUEUE_N VALID
IX RULE SET STREAMS_QUEUE_R VALID
IX SEQUENCE AQ$_ORDERS_QUEUETABLE_N VALID
IX SEQUENCE AQ$_STREAMS_QUEUE_TABLE_N VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_G VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_H VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_I VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_L VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_S VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_T VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_C VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_G VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_H VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_I VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_L VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_S VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_T VALID
IX TABLE ORDERS_QUEUETABLE VALID
IX TABLE STREAMS_QUEUE_TABLE VALID
IX TYPE ORDER_EVENT_TYP VALID
IX VIEW AQ$ORDERS_QUEUETABLE VALID
IX VIEW AQ$ORDERS_QUEUETABLE_R VALID
IX VIEW AQ$ORDERS_QUEUETABLE_S VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE_R VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE_S VALID
IX VIEW AQ$_ORDERS_QUEUETABLE_F VALID
IX VIEW AQ$_STREAMS_QUEUE_TABLE_F VALID
OE FUNCTION GET_PHONE_NUMBER_F VALID
OE INDEX ACTION_TABLE_MEMBERS VALID
OE INDEX CUSTOMERS_PK VALID
OE INDEX CUST_ACCOUNT_MANAGER_IX VALID
OE INDEX CUST_EMAIL_IX VALID
OE INDEX CUST_LNAME_IX VALID
OE INDEX CUST_UPPER_NAME_IX VALID
OE INDEX INVENTORY_IX VALID
OE INDEX INV_PRODUCT_IX VALID
OE INDEX ITEM_ORDER_IX VALID
OE INDEX ITEM_PRODUCT_IX VALID
OE INDEX LINEITEM_TABLE_MEMBERS VALID
OE INDEX ORDER_ITEMS_PK VALID
OE INDEX ORDER_ITEMS_UK VALID
OE INDEX ORDER_PK VALID
OE INDEX ORD_CUSTOMER_IX VALID
OE INDEX ORD_ORDER_DATE_IX VALID
OE INDEX ORD_SALES_REP_IX VALID
OE INDEX PRD_DESC_PK VALID
OE INDEX PRODUCT_INFORMATION_PK VALID
OE INDEX PROD_NAME_IX VALID
OE INDEX PROD_SUPPLIER_IX VALID
OE INDEX PROMO_ID_PK VALID
OE INDEX WAREHOUSES_PK VALID
OE INDEX WHS_LOCATION_IX VALID
OE LOB EXTRADATA46_L VALID
OE LOB NAMESPACES47_L VALID
OE SEQUENCE ORDERS_SEQ VALID
OE SYNONYM COUNTRIES VALID
OE SYNONYM DEPARTMENTS VALID
OE SYNONYM EMPLOYEES VALID
OE SYNONYM JOBS VALID
OE SYNONYM JOB_HISTORY VALID
OE SYNONYM LOCATIONS VALID
OE TABLE ACTION_TABLE VALID
OE TABLE CATEGORIES_TAB VALID
OE TABLE CUSTOMERS VALID
OE TABLE INVENTORIES VALID
OE TABLE LINEITEM_TABLE VALID
OE TABLE ORDERS VALID
OE TABLE ORDER_ITEMS VALID
OE TABLE PRODUCT_DESCRIPTIONS VALID
OE TABLE PRODUCT_INFORMATION VALID
OE TABLE PRODUCT_REF_LIST_NESTEDTAB VALID
OE TABLE PROMOTIONS VALID
OE TABLE PURCHASEORDER VALID
OE TABLE SUBCATEGORY_REF_LIST_NESTEDTAB VALID
OE TABLE WAREHOUSES VALID
OE TRIGGER INSERT_ORD_LINE VALID
OE TRIGGER ORDERS_ITEMS_TRG VALID
OE TRIGGER ORDERS_TRG VALID
OE TRIGGER PURCHASEORDER$xd VALID
OE TYPE ACTIONS_T VALID
OE TYPE ACTION_T VALID
OE TYPE ACTION_V VALID
OE TYPE CATALOG_TYP $VSN_1 VALID
OE TYPE CATALOG_TYP VALID
OE TYPE CATEGORY_TYP $VSN_1 VALID
OE TYPE CATEGORY_TYP VALID
OE TYPE COMPOSITE_CATEGORY_TYP $VSN_1 VALID
OE TYPE COMPOSITE_CATEGORY_TYP VALID
OE TYPE CORPORATE_CUSTOMER_TYP VALID
OE TYPE CUSTOMER_TYP VALID
OE TYPE CUST_ADDRESS_TYP VALID
OE TYPE INVENTORY_LIST_TYP VALID
OE TYPE INVENTORY_TYP VALID
OE TYPE LEAF_CATEGORY_TYP $VSN_1 VALID
OE TYPE LEAF_CATEGORY_TYP VALID
OE TYPE LINEITEMS_T VALID
OE TYPE LINEITEM_T VALID
OE TYPE LINEITEM_V VALID
OE TYPE ORDER_ITEM_LIST_TYP VALID
OE TYPE ORDER_ITEM_TYP VALID
OE TYPE ORDER_LIST_TYP VALID
OE TYPE ORDER_TYP VALID
OE TYPE PART_T VALID
OE TYPE PHONE_LIST_TYP VALID
OE TYPE PRODUCT_INFORMATION_TYP VALID
OE TYPE PRODUCT_REF_LIST_TYP VALID
OE TYPE PURCHASEORDER_T VALID
OE TYPE REJECTION_T VALID
OE TYPE SHIPPING_INSTRUCTIONS_T VALID
OE TYPE SUBCATEGORY_REF_LIST_TYP VALID
OE TYPE WAREHOUSE_TYP VALID
OE TYPE BODY CATALOG_TYP VALID
OE TYPE BODY COMPOSITE_CATEGORY_TYP VALID
OE TYPE BODY LEAF_CATEGORY_TYP VALID
OE VIEW ACCOUNT_MANAGERS VALID
OE VIEW BOMBAY_INVENTORY VALID
OE VIEW CUSTOMERS_VIEW VALID
OE VIEW OC_CORPORATE_CUSTOMERS VALID
OE VIEW OC_CUSTOMERS VALID
OE VIEW OC_INVENTORIES VALID
OE VIEW OC_ORDERS VALID
OE VIEW OC_PRODUCT_INFORMATION VALID
OE VIEW ORDERS_VIEW VALID
OE VIEW PRODUCTS VALID
OE VIEW PRODUCT_PRICES VALID
OE VIEW SYDNEY_INVENTORY VALID
OE VIEW TORONTO_INVENTORY VALID
PM INDEX PRINTMEDIA_PK VALID
PM TABLE PRINT_MEDIA VALID
PM TABLE TEXTDOCS_NESTEDTAB VALID
PM TYPE ADHEADER_TYP VALID
PM TYPE TEXTDOC_TAB VALID
PM TYPE TEXTDOC_TYP VALID
SH INDEX CHANNELS_PK VALID
SH INDEX COUNTRIES_PK VALID
SH INDEX CUSTOMERS_PK VALID
SH INDEX DR$SUP_TEXT_IDX$KD VALID
SH INDEX DR$SUP_TEXT_IDX$KR VALID
SH INDEX DR$SUP_TEXT_IDX$X VALID
SH INDEX PRODUCTS_PK VALID
SH INDEX PRODUCTS_PROD_CAT_IX VALID
SH INDEX PRODUCTS_PROD_SUBCAT_IX VALID
SH INDEX PROMO_PK VALID
SH INDEX SUP_TEXT_IDX VALID
SH INDEX TIMES_PK VALID
SH TABLE CHANNELS VALID
SH TABLE COUNTRIES VALID
SH TABLE CUSTOMERS VALID
SH TABLE DR$SUP_TEXT_IDX$I VALID
SH TABLE DR$SUP_TEXT_IDX$K VALID
SH TABLE DR$SUP_TEXT_IDX$N VALID
SH TABLE DR$SUP_TEXT_IDX$U VALID
SH TABLE PRODUCTS VALID
SH TABLE PROMOTIONS VALID
SH TABLE SALES_TRANSACTIONS_EXT VALID
SH TABLE SUPPLEMENTARY_DEMOGRAPHICS VALID
SH TABLE TIMES VALID
209 rows selected.
Data types used
OWNER DATA_TYPE DATA_TYPE_OWNER DAT COUNT(*)
------ ----------------------------------- ---------------- --- ----------
PM ADHEADER_TYP PM 1
IX ANYDATA SYS 7
IX AQ$_SIG_PROP SYS 4
PM BFILE 1
PM BLOB 2
SH BLOB 1
HR CHAR 3
IX CHAR 2
OE CHAR 2
SH CHAR 5
IX CLOB 2
PM CLOB 2
OE CUSTOMER_TYP OE REF 1
OE CUST_ADDRESS_TYP OE 3
HR DATE 3
IX DATE 6
OE DATE 2
SH DATE 15
OE INTERVAL YEAR(2) TO MONTH 3
OE INVENTORY_LIST_TYP OE 1
PM NCLOB 1
HR NUMBER 21
IX NUMBER 107
OE NUMBER 80
PM NUMBER 2
SH NUMBER 76
OE NVARCHAR2 7
IX ORDER_EVENT_TYP IX 3
OE ORDER_ITEM_LIST_TYP OE 1
OE ORDER_LIST_TYP OE 2
OE PHONE_LIST_TYP OE 3
IX RAW 32
IX ROWID 6
SH ROWID 2
OE SDO_GEOMETRY MDSYS 2
PM TEXTDOC_TAB PM 1
IX TIMESTAMP(6) 25
OE TIMESTAMP(6) WITH LOCAL TIME ZONE 1
IX TIMESTAMP(6) WITH TIME ZONE 8
IX TIMESTAMP(9) 2
HR VARCHAR2 24
IX VARCHAR2 128
OE VARCHAR2 53
SH VARCHAR2 45
OE WAREHOUSE_TYP OE 1
OE XMLTYPE SYS 2
46 rows selected.
XML tables
OWNER TABLE_NAME SCHEMA_OWNER STORAGE_TYPE
------ ------------------------------ ---------------- --------------------
OE PURCHASEORDER OE OBJECT-RELATIONAL
1 row selected.
All objects named 'SYS%' (LOBs etc)
OWNER OBJECT_TYPE STATUS COUNT(*)
------ -------------------- -------- ----------
IX INDEX VALID 16
OE INDEX VALID 24
PM INDEX VALID 9
SH INDEX VALID 3
IX LOB VALID 3
OE LOB VALID 13
PM LOB VALID 7
SH LOB VALID 1
IX TABLE VALID 2
OE TYPE VALID 5
10 rows selected.
All constraints
OWNER CONSTRAINT_TYPE STATUS VALIDATED GENERATED COUNT(*)
------ -------------------- -------- ---------------- ---------------- ----------
IX Check or Not Null ENABLED VALIDATED GENERATED NAME 4
OE Check or Not Null ENABLED VALIDATED GENERATED NAME 2
SH Check or Not Null ENABLED VALIDATED GENERATED NAME 103
HR Check or Not Null ENABLED VALIDATED USER NAME 15
OE Check or Not Null ENABLED VALIDATED USER NAME 13
OE Foreign key ENABLED NOT VALIDATED USER NAME 2
SH Foreign key ENABLED NOT VALIDATED USER NAME 1
HR Foreign key ENABLED VALIDATED USER NAME 10
OE Foreign key ENABLED VALIDATED USER NAME 7
PM Foreign key ENABLED VALIDATED USER NAME 1
SH Primary key DISABLED NOT VALIDATED USER NAME 1
SH Primary key ENABLED NOT VALIDATED USER NAME 6
IX Primary key ENABLED VALIDATED GENERATED NAME 13
OE Primary key ENABLED VALIDATED GENERATED NAME 3
SH Primary key ENABLED VALIDATED GENERATED NAME 2
HR Primary key ENABLED VALIDATED USER NAME 7
OE Primary key ENABLED VALIDATED USER NAME 8
PM Primary key ENABLED VALIDATED USER NAME 1
HR Read only view ENABLED NOT VALIDATED GENERATED NAME 1
IX Read only view ENABLED NOT VALIDATED GENERATED NAME 8
OE Unique key ENABLED VALIDATED GENERATED NAME 6
PM Unique key ENABLED VALIDATED GENERATED NAME 1
HR Unique key ENABLED VALIDATED USER NAME 1
23 rows selected.
All dimensions
no rows selected
All granted roles
GRANTED_ROLE GRANTEE
------------------------- -------
AQ_ADMINISTRATOR_ROLE IX
AQ_USER_ROLE IX
CONNECT IX
CONNECT PM
RESOURCE BI
RESOURCE HR
RESOURCE IX
RESOURCE OE
RESOURCE PM
RESOURCE SH
SELECT_CATALOG_ROLE IX
SELECT_CATALOG_ROLE SH
XDBADMIN OE
13 rows selected.
All granted system privileges
PRIVILEGE GRANTEE
------------------------- -------
ALTER SESSION BI
ALTER SESSION HR
ALTER SESSION IX
ALTER SESSION SH
CREATE CLUSTER BI
CREATE CLUSTER IX
CREATE CLUSTER SH
CREATE DATABASE LINK BI
CREATE DATABASE LINK HR
CREATE DATABASE LINK IX
CREATE DATABASE LINK OE
CREATE DATABASE LINK SH
CREATE DIMENSION SH
CREATE INDEXTYPE IX
CREATE MATERIALIZED VIEW OE
CREATE MATERIALIZED VIEW SH
CREATE OPERATOR IX
CREATE PROCEDURE IX
CREATE RULE IX
CREATE RULE SET IX
CREATE SEQUENCE BI
CREATE SEQUENCE HR
CREATE SEQUENCE IX
CREATE SEQUENCE SH
CREATE SESSION BI
CREATE SESSION HR
CREATE SESSION IX
CREATE SESSION OE
CREATE SESSION SH
CREATE SYNONYM BI
CREATE SYNONYM HR
CREATE SYNONYM IX
CREATE SYNONYM OE
CREATE SYNONYM SH
CREATE TABLE BI
CREATE TABLE IX
CREATE TABLE SH
CREATE TRIGGER IX
CREATE TYPE IX
CREATE VIEW BI
CREATE VIEW HR
CREATE VIEW IX
CREATE VIEW OE
CREATE VIEW SH
QUERY REWRITE OE
QUERY REWRITE SH
SELECT ANY DICTIONARY IX
UNLIMITED TABLESPACE BI
UNLIMITED TABLESPACE HR
UNLIMITED TABLESPACE IX
UNLIMITED TABLESPACE OE
UNLIMITED TABLESPACE PM
UNLIMITED TABLESPACE SH
53 rows selected.
All granted object privileges
OWNER TABLE_NAME PRIVILEGE GRANTEE
------ ------------------------------ ------------------------- -------
HR COUNTRIES REFERENCES OE
HR COUNTRIES SELECT OE
HR DEPARTMENTS SELECT OE
HR EMPLOYEES REFERENCES OE
HR EMPLOYEES SELECT OE
HR JOBS SELECT OE
HR JOB_HISTORY SELECT OE
HR LOCATIONS REFERENCES OE
HR LOCATIONS SELECT OE
OE BOMBAY_INVENTORY SELECT BI
OE CUSTOMERS SELECT BI
OE CUSTOMERS SELECT PM
OE INVENTORIES SELECT BI
OE INVENTORIES SELECT PM
OE ORDERS SELECT BI
OE ORDERS SELECT PM
OE ORDER_ITEMS SELECT BI
OE ORDER_ITEMS SELECT PM
OE PRODUCTS SELECT BI
OE PRODUCT_DESCRIPTIONS SELECT BI
OE PRODUCT_DESCRIPTIONS SELECT PM
OE PRODUCT_INFORMATION REFERENCES PM
OE PRODUCT_INFORMATION SELECT BI
OE PRODUCT_INFORMATION SELECT PM
OE PRODUCT_PRICES SELECT BI
OE PROMOTIONS SELECT BI
OE SYDNEY_INVENTORY SELECT BI
OE TORONTO_INVENTORY SELECT BI
OE WAREHOUSES SELECT BI
OE WAREHOUSES SELECT PM
SH CHANNELS SELECT BI
SH COUNTRIES SELECT BI
SH CUSTOMERS SELECT BI
SH PRODUCTS SELECT BI
SH PROMOTIONS SELECT BI
SH TIMES SELECT BI
SYS AQ$_UNFLUSHED_DEQUEUES SELECT IX
SYS DATA_FILE_DIR READ SH
SYS DBMS_APPLY_ADM EXECUTE IX
SYS DBMS_AQ EXECUTE IX
SYS DBMS_AQADM EXECUTE IX
SYS DBMS_AQ_BQVIEW EXECUTE IX
SYS DBMS_CAPTURE_ADM EXECUTE IX
SYS DBMS_FLASHBACK EXECUTE IX
SYS DBMS_PROPAGATION_ADM EXECUTE IX
SYS DBMS_STATS EXECUTE HR
SYS DBMS_STATS EXECUTE IX
SYS DBMS_STATS EXECUTE OE
SYS DBMS_STATS EXECUTE PM
SYS DBMS_STATS EXECUTE SH
SYS DBMS_STREAMS_ADM EXECUTE IX
SYS LOG_FILE_DIR READ SH
SYS LOG_FILE_DIR WRITE SH
SYS MEDIA_DIR READ PM
SYS QT78047_BUFFER SELECT IX
SYS QT78074_BUFFER SELECT IX
SYS SS_OE_XMLDIR READ OE
SYS SS_OE_XMLDIR WRITE OE
SYS SUBDIR READ OE
SYS SUBDIR WRITE OE
60 rows selected.
Space usage
OWNER SEGMENT_TYPE SUM(BYTES)
------ -------------------- ----------
HR INDEX 1245184
HR TABLE 393216
HR 1638400
IX INDEX 917504
IX TABLE 524288
IX LOBINDEX 196608
IX LOBSEGMENT 393216
IX 2031616
OE INDEX 3080192
OE TABLE 3932160
OE LOBINDEX 983040
OE LOBSEGMENT 1966080
OE NESTED TABLE 589824
OE 10551296
PM INDEX 196608
PM TABLE 65536
PM LOBINDEX 458752
PM LOBSEGMENT 5373952
PM NESTED TABLE 65536
PM 6160384
SH INDEX 1900544
SH TABLE 19267584
SH LOBINDEX 65536
SH LOBSEGMENT 131072
SH 21364736
41746432
26 rows selected.
Table cardinality relational and object tables
OWNER TABLE_NAME NUM_ROWS
------ ------------------------------ ----------
HR COUNTRIES 25
HR DEPARTMENTS 27
HR EMPLOYEES 107
HR JOBS 19
HR JOB_HISTORY 10
HR LOCATIONS 23
HR REGIONS 4
IX AQ$_ORDERS_QUEUETABLE_G 0
IX AQ$_ORDERS_QUEUETABLE_H 2
IX AQ$_ORDERS_QUEUETABLE_I 2
IX AQ$_ORDERS_QUEUETABLE_L 2
IX AQ$_ORDERS_QUEUETABLE_S 4
IX AQ$_ORDERS_QUEUETABLE_T 0
IX AQ$_STREAMS_QUEUE_TABLE_C 0
IX AQ$_STREAMS_QUEUE_TABLE_G 0
IX AQ$_STREAMS_QUEUE_TABLE_H 0
IX AQ$_STREAMS_QUEUE_TABLE_I 0
IX AQ$_STREAMS_QUEUE_TABLE_L 0
IX AQ$_STREAMS_QUEUE_TABLE_S 1
IX AQ$_STREAMS_QUEUE_TABLE_T 0
IX ORDERS_QUEUETABLE
IX STREAMS_QUEUE_TABLE
IX SYS_IOT_OVER_78061 0
IX SYS_IOT_OVER_78090 0
OE ACTION_TABLE 132
OE CATEGORIES_TAB 22
OE CUSTOMERS 319
OE INVENTORIES 1112
OE LINEITEM_TABLE 2232
OE ORDERS 105
OE ORDER_ITEMS 665
OE PRODUCT_DESCRIPTIONS 8640
OE PRODUCT_INFORMATION 288
OE PRODUCT_REF_LIST_NESTEDTAB 288
OE PROMOTIONS 2
OE PURCHASEORDER 132
OE SUBCATEGORY_REF_LIST_NESTEDTAB 21
OE WAREHOUSES 9
PM PRINT_MEDIA 4
PM TEXTDOCS_NESTEDTAB 12
SH CHANNELS 5
SH COUNTRIES 23
SH CUSTOMERS 55500
SH DR$SUP_TEXT_IDX$I
SH DR$SUP_TEXT_IDX$K
SH DR$SUP_TEXT_IDX$N
SH DR$SUP_TEXT_IDX$U
SH PRODUCTS 72
SH PROMOTIONS 503
SH SALES_TRANSACTIONS_EXT 916039
SH SUPPLEMENTARY_DEMOGRAPHICS 4500
SH TIMES 1826
52 rows selected.
Index cardinality (without LOB indexes)
OWNER INDEX_NAME DISTINCT_KEYS NUM_ROWS
------ ------------------------- ------------- ----------
HR COUNTRY_C_ID_PK 25 25
HR DEPT_ID_PK 27 27
HR DEPT_LOCATION_IX 7 27
HR EMP_DEPARTMENT_IX 11 106
HR EMP_EMAIL_UK 107 107
HR EMP_EMP_ID_PK 107 107
HR EMP_JOB_IX 19 107
HR EMP_MANAGER_IX 18 106
HR EMP_NAME_IX 107 107
HR JHIST_DEPARTMENT_IX 6 10
HR JHIST_EMPLOYEE_IX 7 10
HR JHIST_EMP_ID_ST_DATE_PK 10 10
HR JHIST_JOB_IX 8 10
HR JOB_ID_PK 19 19
HR LOC_CITY_IX 23 23
HR LOC_COUNTRY_IX 14 23
HR LOC_ID_PK 23 23
HR LOC_STATE_PROVINCE_IX 17 17
HR REG_ID_PK 4 4
IX AQ$_STREAMS_QUEUE_TABLE_Y 0 0
OE ACTION_TABLE_MEMBERS 132 132
OE CUSTOMERS_PK 319 319
OE CUST_ACCOUNT_MANAGER_IX 4 319
OE CUST_EMAIL_IX 319 319
OE CUST_LNAME_IX 176 319
OE CUST_UPPER_NAME_IX 319 319
OE INVENTORY_IX 1112 1112
OE INV_PRODUCT_IX 208 1112
OE ITEM_ORDER_IX 105 665
OE ITEM_PRODUCT_IX 185 665
OE LINEITEM_TABLE_MEMBERS 132 132
OE ORDER_ITEMS_PK 665 665
OE ORDER_ITEMS_UK 665 665
OE ORDER_PK 105 105
OE ORD_CUSTOMER_IX 47 105
OE ORD_ORDER_DATE_IX 105 105
OE ORD_SALES_REP_IX 9 70
OE PRD_DESC_PK 8640 8640
OE PRODUCT_INFORMATION_PK 288 288
OE PROD_NAME_IX 3727 8640
OE PROD_SUPPLIER_IX 62 288
OE PROMO_ID_PK 2 2
OE WAREHOUSES_PK 9 9
OE WHS_LOCATION_IX 9 9
PM PRINTMEDIA_PK 4 4
SH CHANNELS_PK 5 5
SH COUNTRIES_PK 23 23
SH CUSTOMERS_PK 55500 55500
SH DR$SUP_TEXT_IDX$KD 0 0
SH DR$SUP_TEXT_IDX$KR 0 0
SH DR$SUP_TEXT_IDX$X 0 0
SH PRODUCTS_PK 72 72
SH PRODUCTS_PROD_CAT_IX 5 72
SH PRODUCTS_PROD_SUBCAT_IX 21 72
SH PROMO_PK 503 503
SH SUP_TEXT_IDX
SH TIMES_PK 1826 1826
57 rows selected.
Below I provided the script that prints this:
PROMPT
PROMPT All named objects and stati
SELECT owner, object_type, object_name, subobject_name, status
FROM dba_objects
WHERE owner in ('HR','OE','SH','PM','IX','BI')
AND object_name NOT LIKE 'SYS%'
ORDER BY 1,2,3,4;
PROMPT
PROMPT Data types used
SELECT owner, data_type, data_type_owner, data_type_mod, COUNT(*)
FROM dba_tab_columns
WHERE owner in ('HR','OE','SH','PM','IX','BI')
GROUP BY owner, data_type, data_type_owner, data_type_mod
ORDER BY 2,1,3,4;
PROMPT
PROMPT XML tables
SELECT owner, table_name, schema_owner, storage_type
FROM dba_xml_tables
WHERE owner in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2;
PROMPT
PROMPT All objects named 'SYS%' (LOBs etc)
SELECT owner, object_type, status, COUNT(*)
FROM dba_objects
WHERE owner in ('HR','OE','SH','PM','IX','BI')
AND object_name LIKE 'SYS%'
GROUP BY owner, object_type, status
ORDER BY 2,1,3;
PROMPT
PROMPT All constraints
SELECT owner,
DECODE (constraint_type ,
'C', 'Check or Not Null' ,
'O', 'Read only view' ,
'P', 'Primary key' ,
'R', 'Foreign key' ,
'U', 'Unique key' ,
'V', 'With check view' ) CONSTRAINT_TYPE ,
status,
validated,
generated,
COUNT(*)
FROM dba_constraints
WHERE owner in ('HR','OE','SH','PM','IX','BI')
GROUP BY owner, constraint_type, status, validated, generated
ORDER BY 2,3,4,5,1;
PROMPT
PROMPT All dimensions
SELECT owner, dimension_name, invalid, compile_state
FROM dba_dimensions
WHERE owner in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2;
PROMPT
PROMPT All granted roles
SELECT granted_role, grantee
FROM dba_role_privs
WHERE grantee in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2;
PROMPT
PROMPT All granted system privileges
SELECT privilege, grantee
FROM dba_sys_privs
WHERE grantee in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2;
PROMPT
PROMPT All granted object privileges
SELECT owner, table_name, privilege, grantee
FROM dba_tab_privs
WHERE grantee in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2,3,4;
PROMPT
PROMPT Space usage
SELECT owner, segment_type, sum(bytes)
FROM dba_segments
WHERE owner in ('HR','OE','SH','PM','IX','BI')
GROUP BY ROLLUP (owner, segment_type);
PROMPT
PROMPT Table cardinality relational and object tables
SELECT owner, table_name, num_rows
FROM dba_all_tables
WHERE owner in ('HR','OE','SH','PM','IX','BI')
ORDER BY 1,2,3;
PROMPT
PROMPT Index cardinality (without LOB indexes)
SELECT owner, index_name, distinct_keys, num_rows
FROM dba_indexes
WHERE owner in ('HR','OE','SH','PM','IX','BI')
AND index_name NOT LIKE 'SYS%'
ORDER BY 1,2,3;
The list all the schemas:
select USERNAME from SYS.ALL_USERS;
USERNAME
------------------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
XS$NULL
GSMADMIN_INTERNAL
GSMUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSFWUSER
ORACLE_OCM
SYS$UMF
DBSNMP
APPQOSSYS
GSMCATUSER
GGSYS
XDB
ANONYMOUS
WMSYS
DVF
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
MDDATA
LBACSYS
DVSYS
PM
PDBADMIN
HR
OE
IX
SH
BI
42 rows selected.
