Friday, 23 February 2018

How to use SQL Tuning Advisor in Oracle SQL Developer?

›
Oracle SQL Developer -> Connections -> Right mouse click on desired database connection name (e.g. XE) -> Click "Open SQL Wo...

How to take screenshot on top window quickly?

›
e.g. Alt key + Print Screen  key -> Then CTRL + V in "mspaint.exe" -> Save it.

How to see both Oracle VM Virtual Box 32bit/64bit options?

›
e.g. If there are only 32 bit Linux distro in the OS list, you should enable virtualization support in BIOS * BIOS -> CMOS Setup Utilit...
Friday, 16 February 2018

e.g. bulk insert

›
SET TIMING ON / CREATE TABLE HR.EMPLOYEES_TEST AS    SELECT *      FROM HR.EMPLOYEES     WHERE ROWNUM < 0; / DECLARE    TYPE TAB...

[ADVICE] Free Database Monitoring Tool

›
You may use following free tool instead of Oracle EM (Enterprise Manager) ASH Viewer (Active Session History)
Friday, 9 February 2018

Naming conventions for database objects

›
index -> [ SHORT_TABLE_NAME ]_[ SHORT_COLUMN_NAME ]_[ INDEX_TYPE ] e.g. EMP_DEPARTMENT_IX constraint -> [ SHORT_TABLE_NAME ]_[ SHOR...
Friday, 2 February 2018

How to debug code in Toad?

›
e.g. Toad for Oracle -> Database -> Schema Browser -> Click HR -> Click Procedures -> Double click to "PROC_EXEC" ...

How to grap and drop table columns in Toad?

›
e.g. Toad for Oracle -> Type HR.EMPLOYEES then Press F4 in Editor -> Select desired columns by CTRL key-> Drag and drop to Editor...
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...