+
−.. -*- 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 serveroutput on;
+
−  set autotrace on statistics;
+
−  set timing on;
+
−
+
−  declare
+
−  begin
+
−    null;
+
−  end;
+
−  /
+
−
+
−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 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
+
−
+
−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....' );
+
−
+
−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.
+
−
+
−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
+
−
+
−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';
+
−
+
−  select sysdate from dual;
+
−
+
−Working with SQL/Plus.
+
−======================
+
−
+
−Show error details::
+
−
+
−  show errors;
+
−
+
−Dump how exactly field stored::
+
−
+
−  select dump(date '2009-08-07') from dual;
+
−