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;

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

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;
Read more