.. -*- 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 stub.
===================
::

  set autotrace on statistics;
  set timing on;

  declare
  begin
    null;
  end;
  /

Using variables::

  declare
    x number;
  begin
    select 1 into x from dual;
  end;
  /

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();

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;

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

Dump how exactly field stored::

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

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;

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.
==========

Timing info about last queries::

  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::

  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
press ``Ctrl+End``.

Alternatively you may wrap you query with (and optionally use hint to disable
optimizations??)::

  select (*) from ( ... ORIGINAL QUERY ... );

Another option is::

  delete plan_table;
  explain plan for ... SQL statement ...;
  select time from plan_table where id = 0;

See:

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm
  $SQL lists statistics on shared SQL area without the GROUP BY clause.
http://stackoverflow.com/questions/22198853/finding-execution-time-of-query-using-sql-developer
  Finding Execution time of query using SQL Developer.
http://stackoverflow.com/questions/3559189/oracle-query-execution-time
  Oracle query execution time.
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html
   When the explanation doesn't sound quite right...

Last table modification time.
=============================
::

  select max(scn_to_timestamp(ora_rowscn)) from TBL;

  select timestamp from all_tab_modifications where table_owner = 'OWNER';
  select timestamp from all_tab_modifications where table_name = 'TABLE';

List of Oracle Reserved Words.
==============================

 * http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

Find time zone
==============

Set TZ data formt::

  alter session set 'YYYY-MM-DD HH24:MI:SS.FF3 TZR';

For system TZ look to TZ in::

  select SYSTIMESTAMP from dual;

For session TZ look to TZ in::

  select CURRENT_TIMESTAMP from dual;

or directly in::

  select SESSIONTIMEZONE from dual;

You can adjust session TZ by::

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

which affect on ``CURRENT_DATE``, ``CURRENT_TIMESTAMP``, ``LOCALTIMESTAMP``.

``DBTIMEZONE`` is set when database is created and can't be altered if the
database contains a table with a ``TIMESTAMP WITH LOCAL TIME ZONE`` column and
the column contains data::

  select DBTIMEZONE from dual;

Find time at timezone::

  select SYSTIMESTAMP at time zone 'GMT' from dual;

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;