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;

How to enable/disable DBA auto task jobs?

Jobs
Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics
Automatic Segment Advisor -> Identifies segments that reorganized to save space
Automatic SQL Tuning Advisor -> Tune high load SQL

Check auto tasks
SQL> SELECT client_name, status FROM dba_autotask_client;

To disable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;

To enable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.enable;

To disable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

To enable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

Ref: https://smarttechways.com/2015/09/03/disable-and-enable-auto-task-job-for-11g-and-12c-version-in-oracle/

e.g. shrink table usage

Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics

SQL>
SELECT owner,
       table_name,
       ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
  FROM dba_tables
 WHERE table_name = 'EMPLOYEES';
-- Check table actual size

SQL>
  SELECT table_name,
         ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
         ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
         (  ROUND ( (blocks * 8) / 1024, 2)
          - ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
            "wasted_space (MB)"
    FROM dba_tables
   WHERE     (ROUND ( (blocks * 8), 2) >
                 ROUND ( (num_rows * avg_row_len / 1024), 2))
         AND table_name = 'EMPLOYEES'
         AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size

SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation

SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix

SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands 

SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation

SQL>
  SELECT table_name,
         ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
         ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
         (  ROUND ( (blocks * 8) / 1024, 2)
          - ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
            "wasted_space (MB)"
    FROM dba_tables
   WHERE     (ROUND ( (blocks * 8), 2) >
                 ROUND ( (num_rows * avg_row_len / 1024), 2))
         AND table_name = 'EMPLOYEES'
         AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again

Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html

DBMS_JOBS vs DBMS_SCHEDULER

-> DBMS_SCHEDULER introduced in 10g releases so before this, we were able to use only DBMS_JOBS for scheduler jobs.

e.g. DBMS_JOBS
SQL>
VARIABLE job_id NUMBER;
BEGIN
  DBMS_JOB.submit (
    job       => :job_id,
    what      => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1 /* 1 Day */');
  COMMIT;
END;
/
PRINT job_id

e.g. DBMS_SCHEDULER
SQL>
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'example_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');
END;
/

Ref for example: https://ozsoyler.blogspot.com.tr/2017/02/how-to-kill-all-inactive-sessions-with.html

How to monitor TEMP tablespace resource usage?

-> Check Total TEMP usage:
SQL>
SELECT B.TOTAL_MB,
       B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
       ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
       ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
  FROM V$SORT_SEGMENT A,
       (SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;

-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
                  S.USERNAME,
                  U.TABLESPACE,
                  S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
                  U.SEGTYPE,
                  U.CONTENTS,
                  U.BLOCKS
    FROM V$SESSION S, V$TEMPSEG_USAGE U
   WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;

-> Find problematic SQL for TEMP
SQL>
SELECT 
      S.SID
     ,S.CLIENT_INFO
     ,S.MACHINE
     ,S.PROGRAM
     ,S.TYPE
     ,S.LOGON_TIME
     ,S.OSUSER
     ,SQ.SORTS
     ,SQ.DISK_READS
     ,SQ.BUFFER_GETS
     ,SQ.ROWS_PROCESSED
     ,SQ.SQLTYPE
     ,SQ.SQL_TEXT
 FROM GV$SESSION S    
    , GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
  --AND s.inst_id= 1
  AND S.SID = 555 
  AND SQ.INST_ID= S.INST_ID;

e.g. simple DDL audit trigger

e.g. DDL audit trigger

CREATE TABLE SYSTEM.audit_ddl_history
(
osuser varchar2(50),
session_user varchar2(50),
host varchar2(50),
terminal varchar2(50),
object_owner varchar2(50),
obejct_type varchar2(50),
object_name varchar2(50),
sysevent varchar2(50),
audit_date date
);

CREATE OR REPLACE TRIGGER SYSTEM.ALPERDB_AUDIT_HISTORY
   AFTER DDL
   ON DATABASE
begin
if (ora_sysevent='GRANT')
  then
  NULL; -- don't care to grant!
  else
    insert into SYSTEM.audit_ddl_history(osuser, session_user, host, terminal, object_owner, obejct_type, object_name, sysevent,audit_date)
    values(
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','SESSION_USER') , 
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
    sysdate
    );
  end if;
end;
/

e.g.
SQL>
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

Ref: https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm

Solution ORA-01031, ORA-01994

Error
ORA-01031: insufficient privileges

Solution
Give sysdba role to sys
SQL> grant sysdba to sys; 

If you get this error ->
ORA-01994: GRANT failed: password file missing or disabled

Run following command;
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=alper entries=2 force=y
-> entries; number of users can login as sysdba role
-> force; enable to overwrite current pwd file

Check remote_login parameter
SQL> show parameter remote_login_passwordfile;

If remote_login_passwordfile is not "EXCLUSIVE";
Change it permanently:
add this into init.ora file;
*.remote_login_passwordfile='EXCLUSIVE'

Check who has sysdba role in database lastly
SQL> select * from V$PWFILE_USERS;