Posts

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...
Post a Comment
Read more

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  c ol 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;
Post a Comment
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;
Post a Comment
Read more

Query to check top 15 Largest Segments and Larger than 10G segments

-
 Top 15  Largest Segments: SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 15; larger than 10G segments: set lines 300 pages 900 col owner for a20 col segment_name for a35 col segment_type for a17 select owner, segment_name, segment_type, sum(bytes)/1024/1024/1024 object_size_GB from  dba_segments where bytes>10737418240 group by owner, segment_name, segment_type order by 4;
Post a Comment
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...
Post a Comment
Read more

Oracle Database Login status with creation time and startup time

-
set pages 1000 lines 180 col HOST_NAME for a29; col name for a12; col DATABASE_ROLE for a18; col startup_time for a20; col DATABASE_ROLE for a20 select name,database_role,a.INST_ID,INSTANCE_NAME,HOST_NAME,to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup_Time",CREATED,LOGINS,open_mode from gv$instance a,gv$database b  where a.INST_ID=b.INST_ID order by INSTANCE_NUMBER,HOST_NAME,INSTANCE_NAME,CREATED;
Post a Comment
Read more
-
Help yourself ...by helping people who need...!
Post a Comment
Read more