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 default, only "NOT_NULL" constraints copy to new table (HR.EMPLOYEES_NEW) if it has (HR.EMPLOYEES). (PK, FK, index, etc. does not copy)

CTAS is faster because UNDO and REDO data does not generate. (It use NOLOGGING AND PARALEL methods)

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;

To manipulate data by "COMMISSION_PCT" column descending order.
SQL> 
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC;

-> This is wrong query cause "NULL" columns show on top. Therefore, "NULLS LAST" keyword should be used.
SQL>
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC NULLS LAST;

ORA-01495 solution

Action
SQL>
ANALYZE TABLE HR.EMPLOYEES LIST CHAINED ROWS INTO CHAINED_ROWS;

Error
ORA-01495: specified chain row table not found

Solution
SQL>
CREATE TABLE HR.CHAINED_ROWS
(
   OWNER_NAME          VARCHAR2 (30),
   TABLE_NAME          VARCHAR2 (30),
   CLUSTER_NAME        VARCHAR2 (30),
   PARTITION_NAME      VARCHAR2 (30),
   SUBPARTITION_NAME   VARCHAR2 (30),
   HEAD_ROWID          ROWID,
   ANALYZE_TIMESTAMP   DATE
);

ORA-02270 solution

Preparation
SQL>
create table A (id number, soid number);

SQL>
create table B (id number);

Action
SQL> 
alter table A
add constraint SOID_B_FK
foreign key (soid)
references B (id);

Error
ORA-02270: no matching unique or primary key for this column-list

Reason
Parent table (B) does not have any primary key for matching with child table (A)

Solution
SQL>
alter table B
add constraint ID_PK 
primary key (id);

ORA-00959 solution

Action
SQL> 
CREATE TABLE HR.EXAMPLE(ABC char(1));

Error
ORA-00959: tablespace '_$deleted$40$0' does not exist

Reason
Defined default tablespace is not available any more for HR user.

Solution
SQL>
ALTER USER HR DEFAULT TABLESPACE NEW_USERS;

ORA-02449 solution

Preparation
SQL>
-- A table is child table
create table A (id number, soid number);

SQL>
-- B table is parent table
create table B (id number); 

SQL>
alter table B
add constraint ID_B_PK 
primary key (id);

SQL>
alter table A
add constraint ID_A_PK 
primary key (id);

SQL>
alter table A
add constraint SOID_B_FK
foreign key (soid)
references B (id);

Action
SQL> 
drop table B;

Error
ORA-02449: unique/primary keys in table referenced by foreign keys

Solution
SQL>
drop table B cascade constraints;

Result
By "cascade constraints" keywords, only SOID_B_FK foreign key removed from Table A, ID_A_PK primary key remains for Table A.

ORA-01720 solution

Preparation
"employees" table exists in HR schema

"v_employees" view exists in HR2 schema;
SQL> grant select on hr.employees_new to hr2;
SQL> create view hr2.v_employees as select * from hr.employees;

User wants to see H2.V_EMPLOYEES view with HR3 user.
Action
SQL> grant select on hr2.v_employees to hr3;

Error
ORA-01720: grant option does not exist for 'HR.EMPLOYEES'

Solution
Append grant option to HR.EMPLOYEES table for HR2 user
SQL> grant select on hr.employees to hr2 with grant option;

Then, grant select permission to HR3 user
SQL> grant select on hr2.v_employees to hr3;

Result
SQL> select * from HR2.v_employees;

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 Worksheet" -> Input and select SQL code block -> Right mouse click then select "SQL Tuning Advisor" or press "CTRL+F12" -> Lastly, analyze the result.

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 Utility -> Virtualization Technology -> [Enabled]

e.g. bulk insert

SET TIMING ON
/
CREATE TABLE HR.EMPLOYEES_TEST
AS
   SELECT *
     FROM HR.EMPLOYEES
    WHERE ROWNUM < 0;
/
DECLARE
   TYPE TABLE_TYP IS TABLE OF HR.EMPLOYEES%ROWTYPE;

   TABLE_ROWS   TABLE_TYP := TABLE_TYP ();

   CURSOR TEST_CURSOR
   IS
      SELECT * FROM HR.EMPLOYEES;

   V_COUNT      NUMBER;
BEGIN
   SELECT COUNT (1) INTO V_COUNT FROM HR.EMPLOYEES;

   DBMS_OUTPUT.PUT_LINE ('TOTAL NUMBER OF ROWS: ' || V_COUNT);

   OPEN TEST_CURSOR;

   LOOP
      FETCH TEST_CURSOR BULK COLLECT INTO TABLE_ROWS LIMIT 10;

      FORALL A IN TABLE_ROWS.FIRST .. TABLE_ROWS.LAST
         INSERT INTO HR.EMPLOYEES_TEST
              VALUES TABLE_ROWS (A);

      COMMIT;
      DBMS_OUTPUT.PUT_LINE (
         'Looping, TEST_CURSOR%rowcount = ' || TEST_CURSOR%ROWCOUNT);
      EXIT WHEN TEST_CURSOR%NOTFOUND;
   
   --EXIT WHEN TEST_CURSOR%ROWCOUNT > 1000000;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      'Exited, TEST_CURSOR%rowcount = ' || TEST_CURSOR%ROWCOUNT);

   CLOSE TEST_CURSOR;
END;
/

[ADVICE] Free Database Monitoring Tool

You may use following free tool instead of Oracle EM (Enterprise Manager)

• ASH Viewer (Active Session History)

Naming conventions for database objects

index -> [SHORT_TABLE_NAME]_[SHORT_COLUMN_NAME]_[INDEX_TYPE]
e.g. EMP_DEPARTMENT_IX

constraint -> [SHORT_TABLE_NAME]_[SHORT_COLUMN_NAME]_[CONSTRAINT_TYPE]
e.g. 
NOT NULL const. -> EMP_EMAIL_NN
FOREIGN KEY (Referential Integrity) const. 
Source table: EMPLOYEES
Column name of source table: DEPARTMENT_ID
-> EMP_DEPT_FK

trigger -> [TABLE_NAME]_TRG
e.g. HR.EMPLOYEES_TRG

sequence -> [TABLE_NAME]_SEQ
e.g. HR.EMPLOYEES_SEQ

type -> [TABLE_NAME]_TYP
e.g. HR.EMPLOYEES_TYP

How to debug code in Toad?

e.g.
Toad for Oracle -> Database -> Schema Browser -> Click HR -> Click Procedures -> Double click to "PROC_EXEC" procedure -> Set breakpoint or Add Watch (optional) -> Press Shift+F7 or Click Debug-Trace Into -> Confirm -> Click Execute

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 by mouse -> Columns should show in Editor

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 scans or index range scans)

Usage
Current setting:
SQL> show parameter optimizer_index_cost_adj;
-- default is 100
-- ranges between 1 and 10000

(options like -> 10 | 100 | 1000)
Change setting permanently:
add this into init.ora file;
*.optimizer_index_cost_adj=10
-- "10" is to force it uses index
-- "100" is the default value
-- "1000" is not to force and it tries to different method (large-table full-table) to search data  

Then restart the instance to perform.

Change setting for current instance - temporary:
SQL> alter system set optimizer_index_cost_adj = 10;

Result
SQL> show parameter optimizer_index_cost_adj;
-- output; 10

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 -> lastly, define primary key -> recompile invalid objects (optional)

SQL>
LOCK TABLE HR.EMPLOYEES_NEW IN EXCLUSIVE MODE NOWAIT
/
ALTER TABLE HR.EMPLOYEES_NEW
ADD (ID NUMBER)
/
DECLARE
   str_exec      VARCHAR2 (250);
   counter       NUMBER := 0;
   last_seq_id   NUMBER;
BEGIN
   FOR cur_node IN (  SELECT ROWID, id
                        FROM HR.EMPLOYEES_NEW
                    ORDER BY HIRE_DATE ASC)
   LOOP
      BEGIN
         str_exec :=
               'update HR.EMPLOYEES_NEW set id =  '
            || counter
            || ' where rowid = '
            || ''''
            || cur_node.ROWID
            || '''';

         --DBMS_OUTPUT.put_line (str_exec);

         EXECUTE IMMEDIATE str_exec;

         COMMIT;

         counter := counter + 1;
      END;
   END LOOP;

   SELECT ID 
     INTO last_seq_id
     FROM (  SELECT ID 
               FROM HR.EMPLOYEES_NEW
              WHERE ID IS NOT NULL
           ORDER BY ID DESC)
    WHERE ROWNUM < 2;

   str_exec :=
         'CREATE SEQUENCE HR.EMPLOYEES_NEW_ID_SEQ START WITH '
      || last_seq_id
      || ' MAXVALUE 999999999999999999999999999999 MINVALUE '
      || last_seq_id
      || ' NOCYCLE NOCACHE NOORDER';

   --DBMS_OUTPUT.put_line (str_exec);

   EXECUTE IMMEDIATE str_exec;
END;
/
CREATE OR REPLACE TRIGGER HR.TRG_EMPLOYEES_NEW_ID
   BEFORE INSERT
   ON HR.EMPLOYEES_NEW
   FOR EACH ROW
BEGIN
   IF :new.ID IS NULL
   THEN
      :new.ID := HR.EMPLOYEES_NEW_ID_SEQ.NEXTVAL;
   END IF;
END TRG_EMPLOYEES_NEW_ID;
/
ALTER TABLE HR.EMPLOYEES_NEW ADD
CONSTRAINT EMPLOYEES_NEW_PK
 PRIMARY KEY (ID)
 ENABLE
 VALIDATE
/
EXEC SYS.UTL_RECOMP.recomp_parallel(4)
/

How to use "Toad for Oracle" Help?

e.g.
Toad for Oracle -> Help -> Contents -> Search "Debugging a Procedure or Function Tutorial"

enq: JI - contention wait event solution

Action
Synchronizing data with remote database(NEW_XE)

SQL>
BEGIN DBMS_SNAPSHOT.REFRESH(LIST =>  'HR.SS_EMPLOYEES_NEW',METHOD => 'F' ); END;

Problem
enq: JI - contention wait event occurs

Determine the problem
SQL>
  SELECT eq_name "Enqueue",
         ev.name "Enqueue Type",
         eq.req_description "Description"
    FROM v$enqueue_statistics eq, v$event_name ev
   WHERE eq.event# = ev.event# AND ev.name = 'enq: JI - contention'
ORDER BY ev.name;

Description -> Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view

Solution I
Kill problematic session
SQL>
  SELECT DISTINCT
            'ALTER SYSTEM KILL SESSION '''
         || SID
         || ','
         || SERIAL#
         || ''' IMMEDIATE;'
            AS KILL_THEM_ALL
    FROM V$SESSION
   WHERE     TYPE <> 'BACKGROUND'
         AND STATUS = 'ACTIVE'
         AND EVENT = 'enq: JI - contention';

Solution II
Check database link
SQL>
SELECT * FROM V$INSTANCE@NEW_XE

e.g. tablespace move

Check tablespaces on database
SQL>
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT';

Delete all records on tables if needs
SQL>
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'HR';

Before movement operation, enable row movement feature
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE ROW MOVEMENT;' FROM ALL_TABLES WHERE TABLESPACE_NAME = 'HR';

Shrink tables
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' SHRINK SPACE;' from ALL_TABLES WHERE TABLESPACE_NAME = 'HR';

If ORA-10631 error occurs;
ALTER TABLE HR.EMPLOYEES_NEW SHRINK SPACE
Error at line 99
ORA-10631: SHRINK clause should not be specified for this object

Solution:
If FUNCTION-BASED NORMAL index stores on related table, this error occurs
1- Drop the function-based index.
2- Shrink the table.
3- Recreate the index again on the table.

Define a fresh tablespace
SQL>
CREATE TABLESPACE HR_NEW DATAFILE
  '/oradata/XE/data.dbf' SIZE 1G AUTOEXTEND ON NEXT 8K MAXSIZE 5G,
  '/oradata/XE/data01.dbf'' SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G,
  '/oradata/XE/data02.dbf SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

If need, move related tablespace to a fresh tablespace
For tables:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE TS_HR_NEW;' from ALL_TABLES WHERE TABLESPACE_NAME = 'TS_HR';

For indexes:
SQL>
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE TS_HR_NEW;'  FROM ALL_INDEXES  WHERE TABLESPACE_NAME='TS_HR';

For lob segments:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ')' || ' STORE AS ( TABLESPACE TS_HR_NEW);' FROM DBA_LOBS WHERE TABLESPACE_NAME = 'TS_HR'

Rebuild unusable index for other schema's indexes:
SQL>
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ;

Gather statistics of tables:
SQL>
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || OWNER || ''''  || ',' || '''' || TABLE_NAME || ''');' FROM ALL_TABLES WHERE TABLESPACE_NAME='TS_HR_NEW';

Check size of data files
SQL>
  SELECT OWNER,
         SEGMENT_NAME,
         SEGMENT_TYPE,
         PARTITION_NAME,
         ROUND (BYTES / (1024 * 1024), 2) SIZE_MB,
         TABLESPACE_NAME
    FROM DBA_SEGMENTS
   WHERE     SEGMENT_TYPE IN ('TABLE',
                              'TABLE PARTITION',
                              'TABLE SUBPARTITION',
                              'INDEX',
                              'INDEX PARTITION',
                              'INDEX SUBPARTITION',
                              'TEMPORARY',
                              'LOBINDEX',
                              'LOBSEGMENT',
                              'LOB PARTITION')
         AND TABLESPACE_NAME LIKE '%TS_HR%'
         --AND SEGMENT_NAME LIKE 'P2010201%'
         --AND partition_name LIKE 'P20100201%'
         --AND segment_type = 'TABLE'
         --AND OWNER = 'HR'
         AND ROUND (BYTES / (1024 * 1024), 2) > 10000
ORDER BY BYTES DESC;

Ref:
https://ozsoyler.blogspot.com.tr/2017/08/eg-table-shrink-usage.html