DBA Insights & Automation Hub and Best Practices

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;

 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;

 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;

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...

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;

Help yourself ...by helping people who need...!