+
−.. -*- 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;
+
−
+
−Tables indexes::
+
−
+
−  select * from USER_INDEXES order by TABLE_NAME;
+
−
+
−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;
+
−
+
−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 currenct user constraints::
+
−
+
−  select * from USER_CONSTRAINTS;
+
−
+
−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;
+
−