Friday, 6 April 2018

How to use SPOOL command in Oracle?

›
WINDOWS e.g. SQL>  sqlplus / as sysdba; SQL> SET LINESIZE 200; SET PAGESIZE 100; -- define a output file for writing SPOOL ...
Tuesday, 3 April 2018

What are pencil hardness grading scales?

›
2H -> H -> F -> HB -> B -> 2B H means Hard (hard-tipped) B means Bold  (soft-tipped) F means Fine Info: from left to r...
Friday, 30 March 2018

What is implicit commit?

›
After every DDL operation, implicit commit occurs such as while defining a new table. e.g. CREATE TABLE HR.EXAMPLE(ABC char(1)); Also w...

blob vs bfile data types

›
BLOB (binary large objects) stores unstructured binary large objects. It is often used for graphic images, video clips, and sounds. The BLO...

How to do CTAS in Oracle?

›
CTAS stands for Create Table As Select e.g. SQL> CREATE TABLE HR.EMPLOYEES_NEW AS     SELECT * FROM HR.EMPLOYEES; -> By defau...

NULLS LAST keyword usage

›
-> HR.EMPLOYEES will be test table. SQL>  DESC HR.EMPLOYEES; e.g. To populate all data SQL>  SELECT * FROM HR.EMPLOYEES; T...

ORA-01495 solution

›
Action SQL> ANALYZE TABLE HR.EMPLOYEES LIST CHAINED ROWS INTO CHAINED_ROWS ; Error ORA-01495 : specified chain row table not found ...
Friday, 23 March 2018

ORA-02270 solution

›
Preparation SQL> create table A (id number, soid number); SQL> create table B (id number); Action SQL>  alter table A add...
Friday, 16 March 2018

ORA-00959 solution

›
Action SQL>  CREATE TABLE HR.EXAMPLE(ABC char(1)); Error ORA-00959: tablespace '_$deleted$40$0' does not exist Reason Def...
Friday, 9 March 2018

ORA-02449 solution

›
Preparation SQL> -- A table is child table create table A (id number, soid number); SQL> -- B table is parent table create tab...
Friday, 2 March 2018

ORA-01720 solution

›
Preparation " employees " table exists in HR schema " v_employees " view exists in HR2 schema; SQL> grant select ...
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...