How to Purge Unified Audit Trail in Oracle
Tablespace SYSAUX Since tablespace SYSAUX has grown too big, so we query V$SYSAUX_OCCUPANTS to check the usage of AUDSYS in SYSAUX like this: SQL> show… Read More »How to Purge Unified Audit Trail in Oracle
SYSTIMESTAMP
Derive Timestamp from SYSTIMESTAMP
SYSTIMESTAMP To have a timestamp as of now, we can use the function SYSTIMESTAMP. SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 09-AUG-23 04.36.54.934270 PM -04:00… Read More »Derive Timestamp from SYSTIMESTAMP
Oracle TO_CHAR Example
TO_CHAR is an overloading function which converts or extracts various types of input values into desired or readable output. TO_CHAR for Datetime TO_CHAR for Number… Read More »Oracle TO_CHAR Example
How to Resolve ORA-01821: date format not recognized
ORA-01821 Tried to display a datetime into a readable format by TO_CHAR function, but it failed with ORA-01821. SQL> set heading off; SQL> select TO_CHAR(sysdate,… Read More »How to Resolve ORA-01821: date format not recognized
Oracle SQL Reserved Words
Besides ANSI SQL reserved words, Oracle has its own reserved words which are conditionally used in the RDBMS. Here we query V$RESERVED_WORDS to list all… Read More »Oracle SQL Reserved Words
How to Unexpire Password
To solve ORA-28001 and ORA-28002, the best way is to follow the rule to change user's password. However, if you had to continue using the… Read More »How to Unexpire Password
How Oracle Alter User Password Expire
Expire User Password In this post, we introduce two ways to expire an user's password in N days. The first one provides no grace time,… Read More »How Oracle Alter User Password Expire
How to Move 11.2.0.2 Database to 11.2.0.4 Environment and Upgrade
Knowing all related directories on the source database Form the bottom, we would like to know the location of spfile, control_file, recovery area, trace dumps,… Read More »How to Move 11.2.0.2 Database to 11.2.0.4 Environment and Upgrade
How to Perform Database Point-In-Time Recovery
Sometimes, DBA may want the database back to a specific time just before a big mistake happened on the database. This is called database point-in-time… Read More »How to Perform Database Point-In-Time Recovery
How to Check Current System Change Number (SCN)
System Change Number Some DBAs will schedule a cron job to capture System Change Number (SCN) periodically to save as a Timestamp/SCN mapping reference. To… Read More »How to Check Current System Change Number (SCN)