[ About | Licence | Contacts ]
Written by Oleksandr Gavenko (AKA gavenkoa), compiled on 2018-02-10 from rev 9e6ad6607a9e.

Oracle database.

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

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

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

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

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

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

grant select on v_$sql to BOB;

select * from v$sql;

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

Note

From docs:

PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

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.

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;

Modify output format:

set pagesize 40;
set linesize 400;