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; /
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;
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;
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;
Show error details:
show errors;
Modify output format:
set pagesize 40; set linesize 400;
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;
List all tables:
select * from DBA_TABLES;
List table sizes:
select from DBA_SEGMENTS;
Schema sizes:
select OWNER, sum(BYTES)/1024/1024 as MiB from DBA_SEGMENTS group by OWNER;
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:
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;
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;
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;
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);
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;
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 press Ctrl+End.
Alternatively you may wrap you query with (and optionally use hint to disable optimizations??):
select count(*) 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:
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';
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;
Format select data as insert statements with /*insert*/ hind (use F5 key in Oracle developer):
select /*insert*/ * from tbl;
Another useful hint:
select /*csv*/ * from tbl;
Alternatively enable output as insert statments (use F5 key in Oracle developer):
set sqlformat insert; select * from tbl; spool /home/user/insert.sql; select * from tbl where ...; spool off;
Another sqlformat:
set sqlformat default; set sqlformat csv; set sqlformat html; set sqlformat xml; set sqlformat json;
Login as sysdba:
sqlplus / as sysdba
Check expiration date for user:
select EXPIRY_DATE from DBA_USERS where USERNAME = 'me'; select USERNAME, ACCOUNT_STATUS from DBA_USERS where USERNAME = 'me';
Connect as sysdba to the database and reset password and unlock user:
alter user <USER> identified by <PASSWORD>; alter user <USER> account unlock;
or with single request:
alter user <USER> identified by <PASSWORD> account unlock;
To make password lasts infinitely check which profile is used assigned:
select USERNAME, PROFILE from DBA_USERS;
and check settings for this profile:
select * from DBA_PROFILES where PROFILE = 'SA';
and reset limits for password lifetime:
alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;
You may create separate profile with its own settings:
create profile MY; alter profile MY PASSWORD_LIFE_TIME UNLIMITED; alter user <USER> profile MY;
To completely disable all password checks:
alter profile DEFAULT limit COMPOSITE_LIMIT UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED;