How to Improve SQL Performance
SQL statements including queries and DML sometimes execute with the different index because of the following reasons: Missing index The target index might be dropped… Read More »How to Improve SQL Performance
Oracle SQL
How to Disable All Referencing Constraints Before Importing Data
Disable Constraints Before you can import with table_exists_action=truncate, you have to disable all referencing constraints. Otherwise, you may get the error: ORA-02266: unique/primary keys in… Read More »How to Disable All Referencing Constraints Before Importing Data
How to Recover Truncated Tables in Oracle
Truncate Table can destroy all data of a table with little footprints (No undo, little redo), only metadata left there. If you are responsible for… Read More »How to Recover Truncated Tables in Oracle
How to Resolve ORA-01406: fetched column value was truncated
Found ORA-01046 in the alert log of a 8i database:...Errors in file /oracle/admin/orcl/bdump/orcl_snp2_43589.trc:ORA-12012: error on auto execute of job 6859ORA-12008: error in snapshot refresh pathORA-01406:… Read More »How to Resolve ORA-01406: fetched column value was truncated
How to Change the Owner of Tables
The following statement is wrong if you are trying to change the owner of a table via rename clause:SQL> alter table hr.employees rename to sh.employees;alter… Read More »How to Change the Owner of Tables
How Oracle Kill Blocking Sessions
After the blocking sessions are identified, DBA should notify the session owners and leave them a chance to exit sessions gracefully by themselves. If things… Read More »How Oracle Kill Blocking Sessions
How to Resolve ORA-12004: REFRESH FAST cannot be used for snapshot
Fast refreshing a materialized view requires a view log existed on the master table to make it function well, if you got ORA-12004, this could… Read More »How to Resolve ORA-12004: REFRESH FAST cannot be used for snapshot
How to Limit Number of Rows Returned in a Query
You can order a specific column in a query by adding ORDER BY clause, but the result always returns and lists all the rows that… Read More »How to Limit Number of Rows Returned in a Query
How to Get the Execution Plan of a Specific Statement
Sometime, there is no OEM around to check the explain plan of a specific statement. We need a bare way to do the job. Here… Read More »How to Get the Execution Plan of a Specific Statement
How to Set Autocommit ON and OFF
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… Read More »How to Set Autocommit ON and OFF