Find the external table details along with dirctories

-


col OWNER for a20
col TABLE_NAME for a30
col DIRECTORY_PATH for a50
set lines 250 pages 100
select a.OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,DIRECTORY_PATH from dba_external_tables a, dba_directories b where a.DEFAULT_DIRECTORY_NAME=b.DIRECTORY_NAME order by a.OWNER;

or 
col TABLE_NAME for a30
col DIRECTORY_PATH for a50
set lines 250 pages 100
select distinct b.OWNER,DEFAULT_DIRECTORY_NAME,DIRECTORY_PATH from dba_external_tables a, dba_directories b where a.DEFAULT_DIRECTORY_NAME=b.DIRECTORY_NAME;

Comments

Post a Comment

Popular posts from this blog

Extraction Multiple SQL Workflow for Automation.

-
 Step 1: Prepare the Folder Create a folder with the SER number under: /monito_sam/extract_output/Extracts Copy all required *.sql files into this folder. Example: cd /monito_sam/extract_output/Extracts mkdir SRXXXXTEST ls -lrt SRXXXXTEST -rwxrwx--- 1 oracle dba 295 Nov 12 23:04 test_sam.sql Step 2: Execute the Script Syntax: ./ run_sql.sh <Complete_PATH_OF_SQLS_FOLDER> To run in background with nohup : nohup ./run_sql.sh <Complete_PATH_OF_SQLS_FOLDER >/dev/null 2>&1 & Example: nohup ./run_sql.sh /monito_sam/extract_output/Extracts/SRXXXXTEST >/dev/null 2>&1 & Step 3: Post-Execution After completion: - The script compresses all generated *.csv files into .gz format inside the same folder. - Grants appropriate access permissions. Check nohup job status: jobs -l Verify output: cd /monito_sam/extract_output/Extracts/SRXXXXTEST Step 4: Email Confirmation Upon successful completion, an email notification will be triggered automatically. We can updat...
Read more

Datapump : Import run slow during DB migration from 12c to 19c

-
DB details: SAMDB and DB Size: 741.54 Export completed Time: 01:46:27 with parallel16 Import work: The FULL Import began with the default parallels, namely 200GB of data were imported in 2 hours, but the rest of the tables took longer to load, as evidenced by the message "enq: Data pump "TM – contention" event It has taken more than 20 hours, but the data import for "“MD_OWNER"."PARTY_RELATION_UPDATES_AUDIT” and "MD_OWNER"."ATTRIBUTE_DELTA_AUDIT" has not been completed. Finally, an import failed due to a fatal error. Huge row count mismatch in “MD_OWNER.PARTY_RELATION_UPDATES_AUDIT” as observed. As a result, " PARTY_RELATION_UPDATES_AUDIT " performed the re-import, which took more than 8hr but did not complete. Performed the Metadata import and Data import option. But, during the data load observed “ enq: TM – contention ” and data loading with “INSERT /*+ APPE...
Read more

How to drop an user?

-
 SQL> drop user 8900308 cascade; drop user 8900908 cascade    *ERROR at line 1: ORA-01935: missing user or role name SQL> !oerr ora 1935 01935, 00000, "missing user or role name" // *Cause:  A user or role name was expected. // *Action: Specify a user or role name. SQL> drop user "8900908" cascade; User dropped. Normal user : Syntax: Drop user <username> cascade;
Read more