ORA-01940: cannot DROP a user that is currently logged in

Reference: http://www.dba-oracle.com/t_ora_01940_cannot_drop_user.htm ORA-01940: cannot DROP a user that is currently logged in Cause: An attempt was made to drop a user that was currently logged in. Action: Make sure the user is logged out, then re-execute the command.   The ORA-01940 can always be cured by bouncing the source and replicated instance.  First, double-check to ensure that […]

Oracle 19C commands

ALTER PLUGGABLE DATABASE PDB01,PDB02 close immediate; ALTER PLUGGABLE DATABASE PDB01,PDB02 OPEN READ WRITE; SQL> col value format a60 SQL> select * from V$DIAG_INFO ; INST_ID NAME VALUE CON_ID ———- —————————— ———————————————————— ———- 1 Diag Enabled TRUE 0 1 ADR Base /u01/app/logs/oracle 0 1 ADR Home /u01/app/logs/oracle/diag/rdbms/CDB_a/CDB 0 1 Diag Trace /u01/app/logs/oracle/diag/rdbms/CDB_a/CDB/trace 0 1 Diag Alert […]

Add new redo group and change size of redo

Add new redo group and change size of redo https://docs.oracle.com/database/121/OSTMG/GUID-42519F90-AB27-46A8-9E59-18393C56C375.htm#OSTMG13960 1. query SQL> Select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log; GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS ———- ———- ———- ————— ———- —————- 1 1 13937 100 2 ACTIVE 2 1 13938 100 2 ACTIVE 3 1 13939 100 2 CURRENT 4 1 13936 100 2 INACTIVE SQL> select […]

Query archive log:

Query archive log: SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE, SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB FROM V$ARCHIVED_LOG GROUP BY TRUNC(COMPLETION_TIME) ORDER BY 1; ARCHIVED_ SIZE_IN_MB ——— ———- 06-NOV-20 3423.69922 07-NOV-20 747.664063 08-DEC-20 25235.2773 alter session set nls_date_format = ‘YYYY-MM-DD HH24’; SELECT TRUNC(COMPLETION_TIME, ‘HH’) ARCHIVED_DATE_HOUR, SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB FROM V$ARCHIVED_LOG GROUP BY […]

ORA-12514 caused by Archive log destination full in Oracle 19C:

ORA-12514 caused by Archive log destination full in Oracle 19C: Symptom: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [oracle@oraclevm01 dump]$ tnsping pdb01 Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = […]