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

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%\AppData\Roaming\SQL Developer\system17.3.1.279.0537\o.jdeveloper.db.connection\connections.xml

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 byte at most (if db charset is AL32UTF8)
-> col1 can store 3 byte at most (if db charset is UTF8)
-> col3 stores string according to NLS_LENGTH_SEMANTICS parameter
SQL>
SELECT * FROM  v$nls_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS';

Check db charset
SQL>
SELECT * FROM nls_database_parameters WHERE parameter like '%SET%';

-> NLS_CHARACTERSET set for CHAR, VARCHAR2 and CLOB data types
-> NLS_NCHAR_CHARACTERSET set for NCHAR, NVARCHAR2 and NCLOB (N, National) data types

e.g.
NLS_NCHAR_CHARACTERSET         UTF8
NLS_CHARACTERSET               WE8ISO8859P9

Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm

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='rlwrap sqlplus'

$ sqlplus hr/hr
SQL> select 1 from dual;

Key trick is that while in sqlplus command line, use up or down keys to navigate sql history.

How to define "Code Templates" in SQL Developer?

e.g.
SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" button -> Input Id as "sl", Template as "select * from " -> OK

Usage:
Press "sl" and press "ctrl+space"

Output:
select * from 

ORA-00900, ORA-00911 solution

SQL
DECLARE
   sql_text   VARCHAR (1000);
BEGIN
   sql_text := 'exec P_COMPILE_INVALID_OBJECTS()';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

Error
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Solution
Use 'CALL' instead of 'EXEC' keyword.

DECLARE
   sql_text   VARCHAR (100);
BEGIN
   sql_text := 'call P_COMPILE_INVALID_OBJECTS()';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

SQL
DECLARE
   sql_text   VARCHAR (1000);
BEGIN
   sql_text := 'select sysdate from dual;';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

Error
ORA-00911: invalid character
ORA-06512: at line 8

Solution
Do not put comma (;) at the end of the sql statement.

DECLARE
   sql_text   VARCHAR (100);
BEGIN
   sql_text := 'select sysdate from dual';

   DBMS_OUTPUT.PUT_LINE (sql_text);

   EXECUTE IMMEDIATE sql_text;
END;

How to show line number in SQL Developer?

Tools -> Preferences -> Code Editor -> Line Gutter -> Activate "Show Line Numbers" -> OK

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 variable
> echo %ORACLE_SID%