Friday, 26 January 2018

optimizer_index_cost_adj parameter

›
Purpose The purpose of optimizer_index_cost_adj parameter is to force use index even the cost is high. (decides large-table full-table scan...
Friday, 19 January 2018

How to define primary key on existed table?

›
e.g. steps; lock table -> add new column for primary key -> update data with counter -> define sequence -> define trigger -...

How to use "Toad for Oracle" Help?

›
e.g. Toad for Oracle -> Help -> Contents -> Search "Debugging a Procedure or Function Tutorial"
Friday, 12 January 2018

enq: JI - contention wait event solution

›
Action Synchronizing data with remote database( NEW_XE ) SQL> BEGIN DBMS_SNAPSHOT.REFRESH(LIST =>  'HR.SS_EMPLOYEES_NEW',M...
Friday, 5 January 2018

e.g. tablespace move

›
Check tablespaces on database SQL> SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT'; Delete all records on tables i...
Monday, 18 December 2017

How to backup Oracle SQL Developer connection list?

›
Backup connection.xml file from SQL Developer Setup folder e.g. Oracle SQL Developer Version 17.3.1.279.0537 -> C:\Users\ %USERNAME% ...

char vs byte

›
e.g. SQL>  CREATE TABLE exp_table (    col1   CHAR (1 CHAR),    col2   CHAR (1 BYTE),    col3   CHAR (1) ) -> col1 can store 4...
Friday, 8 December 2017

How to install/use rlwrap?

›
Installation e.g. # rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm   Usage Add alias to .bash_profile file (or .bashrc, etc.) alias sqlplus=...
Wednesday, 6 December 2017

How to define "Code Templates" in SQL Developer?

›
e.g. SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" butto...
Tuesday, 5 December 2017

ORA-00900, ORA-00911 solution

›
SQL DECLARE    sql_text   VARCHAR (1000); BEGIN    sql_text := ' exec P_COMPILE_INVALID_OBJECTS()';    DBMS_OUTPUT.PUT_LINE (s...
Tuesday, 28 November 2017

How to show line number in SQL Developer?

›
Tools -> Preferences -> Code Editor -> Line Gutter -> Activate " Show Line Numbers " -> OK
Wednesday, 8 November 2017

How to export/set ORACLE_SID?

›
Linux export variable $ export ORACLE_SID=XE check variable $ echo $ORACLE_SID Windows set variable > set ORACLE_SID=XE check...
Friday, 3 November 2017

ORA-01950 Solution

›
Action SQL> CREATE TABLE HR2.EMPLOYEES_NEW AS SELECT * FROM HR.EMPLOYEES; Error ORA-01950: no privileges on tablespace 'USERS...
Wednesday, 25 October 2017

ORA-02266 solution

›
Action A table has a PK (Primary Key) B table has a FK (Foreign Key) and referenced to A table's PK -> User tries to truncate tab...

How to disable/enable all triggers for tables?

›
Disable triggers for a table SQL> alter table HR.TRG_EMPLOYEES disable all triggers; Enable triggers for a table SQL> alter table ...
Saturday, 14 October 2017

ORA-01045 solution

›
Error ORA-01045: user HR lacks CREATE SESSION privilege; logon denied Solution Grant CREATE SESSION privilege to 'HR' user SQL...
Friday, 6 October 2017

ORA-28000 solution

›
Error ORA-28000: the account is locked Solution Find the assigned profile for related user SQL> select profile from DBA_USERS where ...
Tuesday, 5 September 2017

How to enable/disable DBA auto task jobs?

›
Jobs Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics Automatic Segment Advisor -> Identifies segm...
Tuesday, 29 August 2017

e.g. shrink table usage

›
Steps: SQL> exec dbms_stats.gather_table_stats ('HR','EMPLOYEES'); -- Gathering table statistics SQL> SELECT own...
Monday, 28 August 2017

DBMS_JOBS vs DBMS_SCHEDULER

›
-> DBMS_SCHEDULER introduced in 10g releases so before this, we were able to use only DBMS_JOBS for scheduler jobs. e.g. DBMS_JOBS SQL...