DML

How to Set Autocommit ON

Posted on
Implicit Commit In the default behavior, if session A wants to see session B’s results, he must be waiting for session B to commit his transaction explicitly or an implicit commit is triggered. Implicit commits will happened under normal session terminations: Gracefully exit. To exit a program will notify database to disconnect from this session […]
Flashback

How to Enable Flashback Database

Posted on
Flashback Database Flashback Database can rewind an entire database back to a state of point-in-time, just before operation failures which involved whole database. The result is the same as database point-in-time recovery (DBPITR) performed by RMAN, but rather simple to operate. If you want to save a recently truncated table, Flashback Table helps none, but […]
Fast Recovery Area

How to Enable Fast Recovery Area (FRA)

Posted on
Fast Recovery Area (FRA) is a very convenient feature for DBA to backup and restore database. It could happen on us to leave FRA option unchecked during database installations. And we may want to enable fast recovery area after the database has been created. Enable Fast Recovery Area There’re 3 statements make your database starting […]
Performance

How to Check Free Memory in SGA Pools

Posted on
The dynamic view gv$sga can tell you how much memory he occupied from OS. They won’t change very often. The real available memory of SGA is provided on gv$sgastat, you can check the free memory of every pool like this.SQL> column “Free (MB)” format 9,999.99;SQL> SELECT inst_id, pool, name, bytes/1024/1024 “Free (MB)” FROM gv$sgastat WHERE name=’free […]
Tablespace

How to Check Oracle Tablespace Usage

Posted on
After having the whole picture about the size of the database, we can further check Oracle tablespace usage solely by SQL. Oracle Tablespace Usage Of course, you can use GUI tools to check the space usage of tablespace in Oracle, but if someday, the only tool you got is sqlplus, a text-based tool, everything must […]
Administration

How to Map Timestamp to SCN or Vice Versa

Posted on
If you are trying to do an incomplete recovery (also called Point-In-Time Recovery, PITR), you may want to know a System Change Number (SCN) using a TIMESTAMP as an input, or vice versa. Oracle provides two functions: timestamp_to_scn and scn_to_timestamp for DBA to map, let’s describe the two functions below: SQL> DESC timestamp_to_scn; FUNCTION timestamp_to_scn RETURNS […]
Administration

How to Check Current System Change Number (SCN)

Posted on
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 achieve this goal, you can try the following statements. SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’; Session altered. SQL> COLUMN SYSTIMESTAMP FORMAT A35; SQL> SELECT systimestamp, current_scn FROM […]