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 […]

Setting , Resetting/Unsetting Database Parameters

https://coskan.wordpress.com/2007/03/07/setting-or-resetting-unsetting-parameters/  https://community.oracle.com/tech/developers/discussion/4027755/need-to-change-hidden-parameter-to-its-default-value https://community.oracle.com/mosc/discussion/3774329/11-2-0-3-many-hidden-parameters-in-init-ora You can query the documented parameters with the query below select name,value,isdefault,isses_modifiable,issys_modifiable, isinstance_modifiable,isdeprecated, from v$parameter You can query the list of undocumented parameters with the script mentioned before “how to view list of hidden parameters” Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values. […]

Enabling supplemental log data

Enabling supplemental log data The following steps check whether supplemental log data is enabled and, if not, enable it. Enter the following command: select supplemental_log_data_min, supplemental_log_data_pk from v$database; If the command returns YES or IMPLICIT, supplemental log data is already enabled. For example, SUPPLEME SUP ——– — YES NO indicates that supplemental log data is […]

North Africa Timezone changes around Ramadan

North Africa Timezone changes around Ramadan Symptom: North Africa clock changes around Ramadan. e.g., Morocco, that is included in DSTv34 which is not included in the current Oracle Database version ( 12.2 and 12.1 ) Oracle Database Cloud Schema Service – Version N/A and later: Updated DST Transitions and New Time Zones in Oracle RDBMS […]

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38706: Cannot turn on FLASHBACK DATABASE logging. Symptom: SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled. SQL> show parameter recovery NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string +FLASH db_recovery_file_dest_size big integer 16G recovery_parallelism integer 0 […]

ORA-38774 while disable archive

ORA-38774 while disable archive Symptom: SQL> Alter database noarchivelog; Alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery – flashback database is enabled SQL> Select name from v$restore_point; no rows selected SQL> select name,flashback_on from v$database; NAME FLASHBACK_ON ——— —————— cdb01 YES Solution: SQL> alter database flashback off; SQL> select name,flashback_on […]

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 = […]