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;

How to start/stop/configure samba service?

If you need, uninstall old samba
# yum erase samba samba-common samba-client 

install tool
# yum install samba4

or upgrade tool
# yum upgrade samba4

configure settings
# vi /etc/samba/smb.conf

e.g.
[share_folder]
 comment = Share Folder
 path = /share_folder
 create mode = 0777
 readonly = no
 browseable = yes
 public = yes

Check configuration of samba
# testparm

check samba service and version
# smbstatus

startup options
# chkconfig smb on
# service smb start

connect to share folder
e.g. 
RUN> \\10.10.10.10\share_folder

remove startup option and stop vnc
# chkconfig smb off
# service smb stop

ORA-28002 solution

Error
ORA-28002: the password will expire within [A] days

Solution
Disable password expiration
SQL> select profile from DBA_USERS where username = 'HR';
-- e.g. DEFAULT

Change password_life_time parameter
SQL> alter profile "DEFAULT" limit password_life_time UNLIMITED;

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

Apply the new setting for user
SQL> alter user "HR" identified by "hr";

SQL PLUS system parameters usage (set/show)

To show parameter in detail
SQL> SHOW DEFINE;

To escape from substitution variable (&):
SQL> SET DEFINE OFF;

To enable DBMS_OUTPUT:
SQL> SET SERVEROUTPUT ON;

To see old and new values of variables:
SQL> SET VERIFY ON;

To calculate total execution time of SQL:
SQL> SET TIMING ON;

To print time on the SQL terminal:
SQL> SET TIME ON;

To see table columns name and types for a table:
SQL> DESC hr.employees@XE;

To see currently logged-in user:
SQL> SHOW user;

e.g. changing the DBID and Database Name

e.g.
Backup database
RMAN> backup database

Mount database
SQL> shu immediate;
SQL> startup mount;

Change DBID
$ nid TARGET=SYS

or

Change both DBID and DBNAME
$ nid TARGET=SYS DBNAME=new_xe

Change database name from parameter file (init.ora)
*.db_name='NEW_XE'

Mount database again
SQL> startup mount;

Open database
SQL> alter database open resetlogs;

Ref: https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544s

SYS_CONTEXT function parameters and examples

Active parameters
ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_NAME, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USER, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID,  SESSIONID, SID, STATEMENTID, TERMINAL

e.g.
SQL> select SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual;
-- HR
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual;
-- HR

Deprecated parameters
AUTHENTICATION_TYPE, CURRENT_USER, CURRENT_USERID, EXTERNAL_NAME

Extra for sys_context
Link

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

Quiesce restricted mode vs Restricted mode

Differences;
* Quiesce restricted option is less raugh than restricted option and they are quite similar.
* In quiesce restricted option, active session allows to continue until it become inactive.
* In restricted session, only new users can login who has "restrict session" privileges, but in quiesce restricted session, system blocks new non-dba users.

commands;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;

check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced

SQL> ALTER SYSTEM UNQUIESCE;

check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Ref: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276

log_buffer, log_checkpoint_timeout and log_checkpoint_interval parameters

Purpose
We are able to tune CKPT process with these paramaters and they should be configured together.

Usage
e.g.
Check current parameter:
SQL> show parameter log

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
log_buffer                           integer     5361664
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1200

Check log file size of one group
SQL> select group#, bytes, blocksize, members from v$log;

    GROUP#      BYTES  BLOCKSIZE    MEMBERS
---------- ---------- ---------- ----------
         1   52428800        512          2
         2   52428800        512          2

Log file size -> 52428800 bytes -> 50 MB
Log buffer should be -> 50 MB * 3 =  150 MB -> 157286400
Log checkpoint timeout could be -> 600 -> 10 minutes
Log checkpoint interval could be -> 0 or 102400 (102400 * 512(OS Block size) -> 52428800 bytes -> 50 MB is equal to log file size)

Apply the setting:
init.ora file:
*.log_buffer=157286400
*.log_checkpoint_interval=102400
*.log_checkpoint_timeout=600

Then restart the instance to perform.

Check lastly:
SQL> show parameter log
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
log_buffer                           integer     157286400
log_checkpoint_interval              integer     102400
log_checkpoint_timeout               integer     600

Deep notes
Log group members should be at least 2, same size, same amount and located in different paths to take backup. (log members are exactly same, 1-> 1)
Log checkpoint timeout parameter means that at most 600 seconds later, dirty blocks are written to database files from SGA.
Log checkpoint interval parameter means that after active log file size exceeds to 50 MB, CKPT is triggered for DBWR. If this parameter is "0", Oracle decides CKPT running time automatically.

plsql_optimize_level and plsql_code_type parameters

Purpose
We are able to use plsql_optimize_level parameter for getting better execution performance on plsql codes. On the other way, plsql_code_type parameter is using to change compilation mode.

Usage
Check current parameter:
SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

Apply the setting:
init.ora file:
*.plsql_optimize_level=3
*.plsql_code_type='NATIVE'

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql;

SQL> shutdown;

SQL> startup;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Check lastly:
SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags                        string
plsql_code_type                      string      NATIVE
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     3
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL

To see current status of objects;
SQL> 
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;

Deep notes
INTERPRETED -> PL/SQL library units compile as bytecode format
NATIVE -> PL/SQL library units compile as native (machine) code format

Ref: https://becomeadba.com/?pages-list

How to find database object via SQL statements?

SQL>
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'

How to solve SQL Developer redrawing problem on Windows?

Problem

Solution

Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK