Precisava criar o schema OE para um laboratório específico, mas percebi que não o tinha em meu banco de dados, mas que também não possuía o seu script de criação no binário instalado (versão 19C):
[oracle@fornix1 schema]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 3 05:12:04 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME='OE';
COUNT(*)
----------
0
SQL> !pwd
/oracle/19.3.0/product/demo/schema
SQL> !ls -lthr
total 68K
-rw-r--r--. 1 oracle oinstall 2.3K Apr 3 2009 drop_sch.sql
-rw-r--r--. 1 oracle oinstall 17K Jun 30 2014 sted_mkplug.sql.dbl
-rw-r--r--. 1 oracle oinstall 27K Jun 30 2014 mkplug.sql
-rw-r-----. 1 oracle oinstall 1.7K Nov 5 2015 mk_dir.sql.sbs
drwxr-xr-x. 2 oracle oinstall 4.0K Apr 17 2019 human_resources
drwxr-xr-x. 2 oracle oinstall 4.0K Apr 17 2019 log
-rw-r--r--. 1 oracle oinstall 1.8K Apr 27 2020 mk_dir.sql
Lendo a documentação da Oracle, percebi que a partir da release 12.2, apenas o script do schema HR está disponível nos binários. Todo o resto deve ser capturado do Github da Oracle:
Descompactando:
[oracle@fornix1 ~]$ pwd
/home/oracle
[oracle@fornix1 ~]$ unzip -d /oracle/19.3.0/product/demo/schema db-sample-schemas-19c.zip
Archive: db-sample-schemas-19c.zip
004d5f3c99dd2e19dee8b72360b55afce458ea32
creating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/CONTRIBUTING.md
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/LICENSE.md
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/README.md
...
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/shipping/qs_main.sql
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/shipping/qs_os.sql
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/shipping/qs_run.sql
inflating: /oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/shipping/qs_ws.sql
[oracle@fornix1 ~]$
Após isso, basta entrar na pasta devida e executar o script oe_main.sql:
[oracle@fornix1 order_entry]$ pwd
/oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/order_entry
[oracle@fornix1 order_entry]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 3 05:25:47 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @oe_main.sql
specify password for OE as parameter 1:
Enter value for 1: oracle
specify default tablespeace for OE as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for OE as parameter 3:
Enter value for 3: TEMP
specify password for HR as parameter 4:
Enter value for 4: oracle
specify password for SYS as parameter 5:
Enter value for 5: oracle
specify directory path for the data files as parameter 6:
Enter value for 6: +DG_DATA
writeable directory path for the log files as parameter 7:
Enter value for 7: /home/oracle
specify version as parameter 8:
Enter value for 8: 19
specify connect string as parameter 9:
Enter value for 9: CORTEX
SP2-0606: Cannot create SPOOL file "/home/oracleoe_oc_19.log"
DROP USER oe CASCADE
*
ERROR at line 1:
ORA-01918: user 'OE' does not exist
old 1: CREATE USER oe IDENTIFIED BY &pass
new 1: CREATE USER oe IDENTIFIED BY oracle
User created.
old 1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new 1: ALTER USER oe DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
User altered.
old 1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new 1: ALTER USER oe TEMPORARY TABLESPACE TEMP
User altered.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Grant succeeded.
Connected.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Session altered.
Session altered.
SP2-0310: unable to open file "__SUB__CWD__/order_entry/coe_19.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/loe_19.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/poe_19.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/oc_main.sql"
SP2-0310: unable to open file "__SUB__CWD__/order_entry/oe_analz.sql"
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
not spooling currently
SQL>
Notei alguns erros reportados, por conta de um diretório não identificado, nesta etapa do script:
DEFINE vscript = __SUB__CWD__/order_entry/coe_&vrs
@&vscript &vrs &pass &pass_sys &connect_string
Graças a um artigo do Tim Hall (AQUI), vi que caso o nosso diretório seja diferente do definido dentro do script, devemos executar o comando abaixo:
[oracle@fornix1 db-sample-schemas-19c]$ cd $ORACLE_HOME/demo/schema/db-sample-schemas-19c
[oracle@fornix1 db-sample-schemas-19c]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
[oracle@fornix1 db-sample-schemas-19c]$
Vou dropar o schema que havia sido criado sem seus objetos:
[oracle@fornix1 db-sample-schemas-19c]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 3 06:16:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DROP USER OE CASCADE;
User dropped.
Executando novamente a criação do OE:
[oracle@fornix1 order_entry]$ pwd
/oracle/19.3.0/product/demo/schema/db-sample-schemas-19c/order_entry
[oracle@fornix1 order_entry]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 3 10:00:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @oe_main.sql
specify password for OE as parameter 1:
Enter value for 1: oracle
specify default tablespeace for OE as parameter 2:
Enter value for 2: USERS
specify temporary tablespace for OE as parameter 3:
Enter value for 3: TEMP
specify password for HR as parameter 4:
Enter value for 4: oracle
specify password for SYS as parameter 5:
Enter value for 5: oracle
specify directory path for the data files as parameter 6:
Enter value for 6: +DG_DATA
writeable directory path for the log files as parameter 7:
Enter value for 7: /home/oracle
specify version as parameter 8:
Enter value for 8: v3
specify connect string as parameter 9:
Enter value for 9: CORTEX
SP2-0606: Cannot create SPOOL file "/home/oracleoe_oc_v3.log"
DROP USER oe CASCADE
*
ERROR at line 1:
ORA-01918: user 'OE' does not exist
old 1: CREATE USER oe IDENTIFIED BY &pass
new 1: CREATE USER oe IDENTIFIED BY oracle
User created.
old 1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new 1: ALTER USER oe DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
User altered.
old 1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new 1: ALTER USER oe TEMPORARY TABLESPACE TEMP
User altered.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Grant succeeded.
Connected.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Session altered.
Session altered.
specify Sample Schema version as parameter 1:
specify password for OE as parameter 2:
PROMPT password for SYS as parameter 3:
specify connect string as parameter 4:
Type created.
Type created.
Table created.
Index created.
Table altered.
Table created.
Index created.
Table altered.
Table created.
Index created.
Index created.
Table altered.
Trigger created.
Table created.
Index created.
Table altered.
Table created.
Index created.
Table altered.
Table created.
Table altered.
Table created.
Index created.
Table altered.
Table created.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Sequence created.
specify password for OE as parameter 1:
PROMPT password for SYS as parameter 2:
specify connect string as parameter 3:
Connected.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
View created.
View created.
Grant succeeded.
Session altered.
Package created.
Warning: Package Body created with compilation errors.
View created.
Package altered.
View altered.
Grant succeeded.
Trigger created.
Synonym created.
Grant succeeded.
Call completed.
Session altered.
Session altered.
Function created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Package body created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Package body created.
no rows selected
Grant succeeded.
Synonym created.
Package created.
no rows selected
Package body created.
no rows selected
Synonym created.
Grant succeeded.
specify password for OE as parameter 1:
PROMPT password for SYS as parameter 2:
specify connect string as parameter 3:
PL/SQL procedure successfully completed.
Connected.
DROP DIRECTORY SS_OE_XMLDIR
*
ERROR at line 1:
ORA-04043: object SS_OE_XMLDIR does not exist
Directory created.
Commit complete.
Connected.
Revoke succeeded.
Connected.
Connected.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Call completed.
Call completed.
PL/SQL procedure successfully completed.
Connected.
Revoke succeeded.
Connected.
Connected.
Revoke succeeded.
Revoke succeeded.
Revoke succeeded.
Package dropped.
Package dropped.
Package dropped.
Package dropped.
Package dropped.
Trigger dropped.
View dropped.
Connected.
Commit complete.
specify Sample Schema version as parameter 1:
specify location for data and control files as parameter 2:
specify location for log files as parameter 3:
specify password for oe as parameter 4:
Setting FEEDBACK OFF for inserts
Setting FEEDBACK ON
1 row created.
1 row created.
Commit complete.
Table altered.
Session altered.
Session altered.
Commit complete.
View created.
View created.
View created.
View created.
View created.
View created.
Function created.
View created.
View created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Session altered.
...creating subschema OC in OE
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type body created.
Type created.
Type body created.
Type created.
Type body created.
Table created.
View created.
View created.
View created.
View created.
View created.
View created.
Trigger created.
Trigger created.
Commit complete.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Type altered.
3 rows updated.
8 rows updated.
6 rows updated.
4 rows updated.
Commit complete.
Type body altered.
Type body altered.
Type body altered.
PL/SQL procedure successfully completed.
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
not spooling currently
SQL>
Validando:
SQL> select username,account_status from dba_users where username='OE';
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
OE
OPEN
1 row selected.
SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER='OE';
COUNT(*)
----------
142
1 row selected.
SQL> CONN OE/oracle
Connected.
SQL> SHOW USER;
USER is "OE"
SQL>
Portuguese
English
Spanish