.. -*- coding: utf-8; -*-

==================

 Oracle database.

==================

.. contents::

   :local:

Oracle database development environment.

========================================

http://en.wikipedia.org/wiki/Oracle_SQL_Developer

  Integrated development environment (IDE) for working with

  SQL/PLSql in Oracle databases.

http://en.wikipedia.org/wiki/SQL*Plus

  An Oracle database client that can run SQL and PL/SQL commands

  and display their results.

http://en.wikipedia.org/wiki/Oracle_Forms

  Is a software product for creating screens that interact with an

  Oracle database. It has an IDE including an object navigator,

  property sheet and code editor that uses PL/SQL.

http://en.wikipedia.org/wiki/Oracle_JDeveloper

  JDeveloper is a freeware IDE supplied by Oracle Corporation. It

  offers features for development in Java, XML, SQL and PL/SQL,

  HTML, JavaScript, BPEL and PHP.

http://en.wikipedia.org/wiki/Oracle_Reports

  Oracle Reports is a tool for developing reports against data

  stored in an Oracle database.

Useful PL/SQL stubs

===================

Declare executable block::

  declare

  begin

    null;

  end;

  /

Using variables::

  declare

    x number;

  begin

    select 1 into x from dual;

  end;

  /

Call procedure::

  create or replace procedure MY_PROC as

  begin

    null;

  end;

  /

  begin

    MY_PROC;

  end;

  /

Call function::

  create or replace function MY_FUN return number as

  begin

    return 42;

  end;

  /

  declare

    x NUMBER;

  begin

    ret := MY_FUN();

  end;

  /

Common client options

=====================

Following options supports Sql*Plus, SQLcl, Oracle Developer.

Enabling printing::

  set serveroutput on;

  exec DBMS_OUTPUT.PUT_LINE('Hello');

  exec DBMS_OUTPUT.DISABLE();

  exec DBMS_OUTPUT.PUT_LINE('Silence');

  exec DBMS_OUTPUT.ENABLE();

Enable printing of query execution time::

  set timing on;

Sql*Plus client options

=======================

Enable printing execution plan stats::

  set autotrace on;

  set autotrace off;

  set autotrace on statistics;

  set autotrace on explain;

Disable printing of ``select`` result, show only stats::

  set autotrace traceonly;

  set autotrace traceonly on explain;

Adjust date format.

===================

::

  column parameter format a32;

  column value format a32;

  select parameter, value from v$nls_parameters;

  alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';

  alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';

  alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';

  alter session set TIME_ZONE = '+06:00';

  select sysdate from dual;

Working with SQL/Plus

=====================

Show error details::

  show errors;

Modify output format::

  set pagesize 40;

  set linesize 400;

Database info

=============

List of users::

  select distinct(OWNER) from ALL_TABLES;

List of current user owned tables::

  select * from USER_TABLES;

  select TABLE_NAME from USER_TABLES;

List of tables by owner::

  select OWNER || '.' || TABLE_NAME from ALL_TABLES

    order by OWNER;

List of current user table sizes::

  select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES) from USER_EXTENTS

    group by SEGMENT_NAME, SEGMENT_TYPE order by sum(BYTES);

  select sum(BYTES) from USER_EXTENTS;

Get LOB stored size::

  select sum(dbms_lob.getchunksize(COL))/1024/1024 MiB from TBL;

Table indexes restricted to user::

  select * from USER_INDEXES order by TABLE_NAME;

Table indexes available to user::

  select * from ALL_INDEXES order by TABLE_NAME;

All table indexes::

  select * from DBA_INDEXES order by TABLE_NAME;

View index columns::

  select * from DBA_IND_COLUMNS;

  select * from ALL_IND_COLUMNS;

  select * from USER_IND_COLUMNS;

Vie index expressions::

  select * from DBA_IND_EXPRESSIONS;

  select * from ALL_IND_EXPRESSIONS;

  select * from USER_IND_EXPRESSIONS;

List of index sizes::

  select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes

    left outer join user_extents on user_extents.segment_name = table_name

    group by index_name, table_name

    order by table_name;

View index statistics::

  select * from DBA_IND_STATISTICS;

  select * from ALL_IND_STATISTICS;

  select * from USER_IND_STATISTICS;

  select * from INDEX_STATS;

List of tables without primary keys::

  select OWNER || '.' || TABLE_NAME from ALL_TABLES

    where TABLE_NAME not in (

      select distinct TABLE_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P'

    ) and OWNER in ('USER1', 'USER2')

    order by OWNER, TABLE_NAME;

List tables that has foreign key to given table::

  select * from SYS.USER_CONSTRAINTS cons

    join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME

    where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME = 'TBL_NAME';

  select * from SYS.USER_CONSTRAINTS cons

    join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME

    where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME like '%/_OLD' escape '/';

List of missing index for foreign key constraint::

  select

    case when b.table_name is null then 'unindexed' else 'indexed' end as status,

    a.table_name      as table_name,

    a.constraint_name as fk_name,

    a.fk_columns      as fk_columns,

    b.index_name      as index_name,

    b.index_columns   as index_columns

  from (

    select

      a.table_name,

      a.constraint_name,

      listagg(a.column_name, ',') within group (order by a.position) fk_columns

    from user_cons_columns a, user_constraints b

    where

      a.constraint_name = b.constraint_name

      and b.constraint_type = 'R'

      and a.owner = b.owner

    group by a.table_name, a.constraint_name

  ) a, (

    select

      table_name,

      index_name,

      listagg(c.column_name, ',') within group (order by c.column_position) index_columns

    from user_ind_columns c

    group by table_name, index_name

  ) b

  where a.table_name = b.table_name(+)

    and b.index_columns(+) like a.fk_columns || '%'

  order by 1 desc, 2;

List of current constraints limited to current user::

  select * from USER_CONSTRAINTS;

List of constraints available to user::

  select * from ALL_CONSTRAINTS;

List of all constraints::

  select * from DBA_CONSTRAINTS;

.. note::

   ``CONSTRAINT_TYPE``:

   * ``C`` (check constraint on a table)

   * ``P`` (primary key)

   * ``U`` (unique key)

   * ``R`` (referential integrity)

   * ``V`` (with check option, on a view)

   * ``O`` (with read only, on a view)

List of tablespaces::

  select distinct TABLESPACE_NAME from USER_TABLES;

List user objects::

  select OBJECT_NAME, OBJECT_TYPE from USER_OBJECTS

    order by OBJECT_TYPE, OBJECT_NAME;

List of current user permissions::

  select * from SESSION_PRIVS;

List of user permissions to tables::

  select * from ALL_TAB_PRIVS where TABLE_SCHEMA not like '%SYS' and TABLE_SCHEMA not like 'SYS%';

List of user privileges::

  select * from USER_SYS_PRIVS

  select * from USER_TAB_PRIVS

  select * from USER_ROLE_PRIVS

List of other users that have access to given user tables::

  select * from ALL_TAB_PRIVS where GRANTOR = 'ME';

Dump how exactly field stored::

  select dump(date '2009-08-07') from dual;

  select dump(sysdate) from dual;

https://dba.stackexchange.com/questions/11047/how-to-retrieve-foreign-key-constraints-data/

  How to retrieve foreign key constraints data.

Managing data files location

============================

To find out where is data files located run as ``sysdba``::

  select * from dba_data_files;

  select * from dba_temp_files;

Above files represent table spaces::

  select * from dba_tablespaces;

Another information about installation::

  select * from v$controlfile;

  select * from v$tablespace;

  select * from v$database;

  show parameter control_files;

Place for dumps::

  show parameter user_dump_dest;

Sessions and locks

==================

List locked objects::

  select * from v$locked_object;

  select * from v$locked_object a, all_objects b

    where a.object_id = b.object_id;

  select b.owner, b.object_name, a.oracle_username, a.os_user_name

    from v$locked_object a, all_objects b

    where a.object_id = b.object_id;

  select

     c.owner,

     c.object_name,

     c.object_type,

     b.sid,

     b.serial#,

     b.status,

     b.osuser,

     b.machine

  from v$locked_object a, v$session b, dba_objects c

  where b.sid = a.session_id and a.object_id = c.object_id;

  select * from DBA_BLOCKERS;

  select * from DBA_DDL_LOCKS;

  select * from DBA_DML_LOCKS;

  select * from DBA_LOCK_INTERNAL;

  select * from DBA_LOCKS;

  select * from DBA_WAITERS;

Kill session::

  alter system kill session 'SID,SERIAL#';

  alter system kill session '361,565';

List session parameters::

  select * from v$parameter;

  select NAME, VALUE from v$parameter;

  show parameters ddl_lock_timeout;

Alter session parameter::

  alter session set ddl_lock_timeout = 10;

Installing express edition

==========================

Disable APEX port

-----------------

Find APEX port in usage::

  select dbms_xdb.GetHttpPort, dbms_xdb.GetFtpPort from dual;

Disable APEX lisener (to free useful 8080 port) from ``system``::

  execute dbms_xdb.SetHttpPort(0);

or move to another port::

  execute dbms_xdb.SetHttpPort(8090);

http://stackoverflow.com/questions/165105/how-to-disable-oracle-xe-component-which-is-listening-on-8080

  How to disable Oracle XE component which is listening on 8080?

http://daust.blogspot.co.il/2006/01/xe-changing-default-http-port.html

  XE: Changing the default http port.

https://erikwramner.wordpress.com/2014/03/23/stop-oracle-xe-from-listening-on-port-8080/

  Stop Oracle XE from listening on port 8080.

Creating user

-------------

From ``system`` account::

  create user BOB identified by 123456;

  alter user BOB account unlock;

  alter user BOB default tablespace USERS;

  alter user BOB temporary tablespace TEMP;

  alter user BOB quota 100M on USERS;

  grant CREATE SESSION, ALTER SESSION to BOB;

  grant CREATE PROCEDURE, CREATE TRIGGER to BOB;

  grant CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM to BOB;

Profiling

=========

``explain plan for`` can be used to show probable execution plan for query::

  explain plan for select 1 from dual;

``plan_table`` should be examined to display result::

  select * from table(dbms_xplan.display);

Remove statistics::

  execute DBMS_STATS.DELETE_SCHEMA_STATS('BOB');

Collect statistics::

  execute DBMS_STATS.GATHER_DATABASE_STATS;

  execute DBMS_STATS.GATHER_SCHEMA_STATS('BOB');

  execute DBMS_STATS.GATHER_TABLE_STATS('BOB', 'EMPLOYEE');

Get actual execution plan of query after executing query::

  select * from table(dbms_xplan.display_cursor);

  select * from table(dbms_xplan.display_cursor(format => 'basic'));

  select * from table(dbms_xplan.display_cursor(format => 'typical'));

  select * from table(dbms_xplan.display_cursor(format => 'all'));

Last queries' execution stats are available through ``v$sql`` view::

  grant select on v_$sql to BOB;

  select * from v$sql;

  select * from v$sql_plan;

  select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql

    order by LAST_LOAD_TIME desc

Improved version of above code::

  column LAST_LOAD_TIME format a20;

  column TIME format a20;

  column MODULE format a10;

  column SQL_TEXT format a60;

  set autotrace off;

  set timing off;

  select * from (

    select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME, MODULE, SQL_TEXT from SYS."V_\$SQL"

      where SQL_TEXT like '%BATCH_BRANCHES%'

      order by LAST_LOAD_TIME desc

    ) where ROWNUM <= 5;

In SQL/Plus query execution time (up to )::

  SET TIMING ON;

  -- do stuff

  SET TIMING OFF;

or::

  set serveroutput on;

  variable n number;

  exec :n := dbms_utility.get_time;

  select ......

  exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' );

In SQL Developer you get execution time in result window. By default SQL

Developer limit output to 50 rows. To run full query select result window nat