KILL Session Marked for Killed Forever

Do you have a session that you have killed, but it seems not to go away ? You issued this several times and it seems it still is marked for killed, but has a lock on the existing table. In order to determine which process ID to kill: a) On Unix: b) On Windows: Then…

Estimate Index Size in OraDB

We can use explain plan for Index create to estimate the size of an Index.   SQL> EXPLAIN PLAN FOR CREATE INDEX DB_OBJECT_IDX ON DB_OBJECT(COL_NAME); SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); … PLAN_TABLE_OUTPUT … estimated index size: 159M bytes But this does not give good enough estimates when majority values in the column are NULL.

Auto start pluggable databases in oracle 12c

Oracle Database 12c does not have mechanism to automatically open pluggable databases i.e. container database starts, but all pluggable database stay in the mount state, not open. To run automatically, we can use a trigger. CREATE or REPLACE trigger OPEN_PDS After Startup on Database BEGIN execute Immediate ‘alter pluggable All Open’; END; to test      …

Analytical Periods, MTD, YTD, Prior Year..

MTD: Month to date is a period starting from the beginning of the current month and ending at the current date. YTD: Year to date is a period starting January 1 (depending on the country) of the current year and ending today. Rolling 12 Months: Prior Year:

Compression in Oracle DB

Compression has been around for quite a while in the Oracle database, but it received signficant improvements in version 11g. For example, now it also works on transaction level, which makes it an option for OLTP databases. It will improve both disk space requirement & db performance, as we see in below example. 1) Create…