set lines 200 col directory_name format a30 col directory_path format a60 select directory_name,directory_path from dba_directories; exit
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Showing posts with label directories. Show all posts
Showing posts with label directories. Show all posts
Wednesday, May 11, 2022
Find available directories on your server
Friday, April 30, 2021
Solution for error when from external tables when installing the SH sample schema
If you are trying to select from the external tables created in the SH sample schemas, and receive an error like this:
This causes the directories created as a part of the installation to contain the variable rather than the physical path, and that will cause the directories to be non-working:
Use an absolute path instead:
And your directories will be created correctly:
You should now be able to select from your external table:
The Oracle 18c sample schemas on github
SQL>select count(*) from SALES_TRANSACTIONS_EXT; select count(*) from SALES_TRANSACTIONS_EXT * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04001: error opening file $ORACLE_HOME/demo/schema/log/ext_1v3.logIt is most likely because you have used the operating system variable $ORACLE_HOME during installation, like this:
sqlplus /nolog
@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ mydb
This causes the directories created as a part of the installation to contain the variable rather than the physical path, and that will cause the directories to be non-working:
CREATE OR REPLACE DIRECTORY DATA_FILE_DIR AS '$ORACLE_HOME/demo/schema/sales_history'; CREATE OR REPLACE DIRECTORY LOG_FILE_DIR AS '$ORACLE_HOME/demo/schema/log';
Use an absolute path instead:
sqlplus /nolog
@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd EXAMPLE TEMP /u01/oracle/18c/demo/schema/log/ mydb
And your directories will be created correctly:
CREATE OR REPLACE DIRECTORY DATA_FILE_DIR AS '/u01/oracle/18c/demo/schema/sales_history'; CREATE OR REPLACE DIRECTORY LOG_FILE_DIR AS '/u01/oracle/18c/demo/schema/log';
You should now be able to select from your external table:
SQL>connect sh/shpwd@mydb Connected. SQL>select count(*) from SALES_TRANSACTIONS_EXT; COUNT(*) ---------- 916039The Oracle 18c installation of the samples schemas is documented here.
The Oracle 18c sample schemas on github
Subscribe to:
Posts (Atom)