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;

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;

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;

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 /*+ APPEND” SQL. This import has taken a long time but didn’t complete it. 

Recommendations

Option 1: 

• Logical Migration, Identify the top tables with a high number row count in the export log(which does not contain partitions) and exclude those tables from full import and import them individually.
• Ensure that tablespaces (including undo and temp) have sufficient space.
• Set parallel=2, to start the individual export for excluded tables.  
• Ensure that the stream pool size remains at least 1 GB.
• Skip the constraints during import and run them again after import. 

Option 2: Oracle Suggested  

• RMAN - Physical migration, which we can complete a least downtime within 8-9 Hrs. 

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;

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