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%

ORA-01950 Solution

Action
SQL> CREATE TABLE HR2.EMPLOYEES_NEW AS SELECT * FROM HR.EMPLOYEES;

Error
ORA-01950: no privileges on tablespace 'USERS'

Solution I
SQL> ALTER USER HR2 QUOTA 1024M ON USERS:

Solution II
SQL> GRANT UNLIMITED TABLESPACE TO HR2;

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 table A

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

Solution I
Disable B table's FK
SQL> alter table HR.B disable constraint FK_B;
-- e.g. schema HR

Solution II
First truncate table B
SQL> truncate table B;

Then truncate table A
SQL> truncate table A;

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 HR.TRG_EMPLOYEES enable all triggers;

Disable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
   STR_EXEC   VARCHAR2 (1000);
BEGIN
   FOR cur_node IN (SELECT owner, table_name
                      FROM all_triggers
                     WHERE owner = 'HR')
   LOOP
      BEGIN
         str_exec :=
               'alter table '
            || cur_node.owner
            || '.'
            || cur_node.table_name
            || ' DISABLE ALL TRIGGERS';
         DBMS_OUTPUT.put_line (str_exec);

         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

Enable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
   STR_EXEC   VARCHAR2 (1000);
BEGIN
   FOR cur_node IN (SELECT owner, table_name
                      FROM all_triggers
                     WHERE owner = 'HR')
   LOOP
      BEGIN
         str_exec :=
               'alter table '
            || cur_node.owner
            || '.'
            || cur_node.table_name
            || ' ENABLE ALL TRIGGERS';
         DBMS_OUTPUT.put_line (str_exec);

         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

ORA-01045 solution

Error
ORA-01045: user HR lacks CREATE SESSION privilege; logon denied

Solution
Grant CREATE SESSION privilege to 'HR' user
SQL> grant CREATE SESSION to HR;

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 username = 'HR';
-- e.g. DEFAULT

Disable failed_login_attempts parameter
SQL> alter profile "DEFAULT" limit failed_login_attempts UNLIMITED;

Check it
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

Apply the new setting for user
SQL> alter user "HR" ACCOUNT UNLOCK;