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