Sorting in a recursive query

Here is a neat utility from Oracle to query a table recursively, and sort results among siblings:

SELECT e.id, e.name, e.manager_id
FROM employee e
START WITH ID = 1
CONNECT BY manager_ID = PRIOR ID
order siblings by e.name;

Note the 'order siblings by' clause. This can be used only with recursive queries and sorts the siblings by the name.

Oracle Cascade Delete

http://www.sandeepsinghal.com/2012/01/14/oracle-cascase-delete/

Oracle SID vs Service Name in JDBC URL

With SID

jdbc:oracle:thin@myhost:port:sid

With Service Name

jdbc:oracle:thin@myhost:port/service

Getting tablespace Size

SELECT tablespace_name,
       SUM (bytes) / 1024 / 1024 current_size,
       autoextensible,
       SUM (maxbytes) / 1024 / 1024 max_size
FROM dba_data_files
WHERE tablespace_name like '%'
GROUP BY tablespace_name, autoextensible
ORDER BY 1

Common sqlplus commands

ACCEPT - Get input from the user 
DEFINE - Declare a variable (short: DEF) 
DESCRIBE - Lists the attributes of tables and other objects (short: DESC) 
EDIT - Places you in an editor so you can edit a SQL command (short: ED) 
EXIT or QUIT - Disconnect from the database and terminate SQL*Plus 
GET - Retrieves a SQL file and places it into the SQL buffer 
HOST - Issue an operating system command (short: !) 
LIST - Displays the last command executed/ command in the SQL buffer (short: L) 
PROMPT - Display a text string on the screen. Eg prompt Hello World!!! 
RUN - List and Run the command stored in the SQL buffer (short: /) 
SAVE - Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql 
SET - Modify the SQL*Plus environment eg. SET PAGESIZE 23 
SHOW - Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc. 
SPOOL - Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst 
START - Run a SQL script file (short: @)

source :

http://www.orafaq.com/wiki/SQL*Plus_FAQ

Oracle - Default Location for data files

To get the existing location

sql > show parameter db_create;

To set a new location

sql> alter system set db_create_file_dest='/home/ssinghal/oradata';

Oracle dba_jobs : Breaking

exec dbms_job.broken(690,true);

where 690 is the job number obtained from
select * from dba_jobs

Oracle databases - startup and shutdown

Shutdown:

$ export ORACLE_SID=db_sid
$ sqlplus /nolog
sql> connect / as sysdba
sql> shutdown immediate

Startup :

$ export ORACLE_SID=db_sid
$ sqlplus /nolog
sql> connect / as sysdba
sql> startup mount
sql> alter database open;

Note: This needs to be done by logging on the machine where oracle database is installed

Database Objects - Finding Dependencies

You come across tables in databases that are being populated by other pl/sql procs, triggers e.t.c what becomes difficult is to figure out which process is actually updating a table.

The following query will list objects that refer a table "TABLE_A" which belongs to a schema "SCHEMA_A"

select name,owner
from dba_dependencies
where referenced_name='TABLE_A' 
and referenced_owner='SCHEMA_A'

Oracle - Enabling Parallelism

If your database server is running on multiple processor system. You can make all your processors conspire to run a query. You might want to do this when the query is known to take a long time and there is no way it would return in sane time. At the same time, be careful to run this on production systems as it may hamper performance of the database.

select /*+ parallel(x 4) */ count(1)
from huge_table
where huge_table.x = 'somevalue'

Bind Parameters in a complex query

Oracle Data dictionary tip :

This is pretty useful when you are running a huge pl/sql code or a complex query and you need to know what bind parameters are being used for a query. helps figure out the iteration you are in amongst many other.

select name, position, datatype, 
       value_string, value_anydata 
from V$SQL_BIND_CAPTURE where sql_id 
IN 
 ( select a.sql_id 
   from v$sql a where sql_id in 
    ( select sql_id 
      from v$session 
      where osuser = 'ssinghal' 
      and machine = 'my_machine_name 
      and status = 'ACTIVE'
     )
 ); 

Long Operations in Oracle

It is nice to know what queries are taking time to finish. The following sql figures out the "long operations" currently being processed


SELECT username,time_remaining,sofar/totalwork,message,target_desc
FROM gv$session_longops where sofar<totalwork
ORDER BY message


Formatting Oracle Timestamp

create table temp (a timestamp)

insert into temp values(sysdate)

select to_char(a,'MM/DD/YYYY HH24:MI:SS:FF3') from temp

http://www.databasejournal.com/features/oracle/article.php/2234501

Oracle Instance Configuration Parameters

To find out the parameters that oracle is using for configuring an instance, logon to that instance using sqlplus and execute the following command.

sql> show parameters

This will display list of all oracle configuration parameters with their current values

Oracle - Common Datafile Operations

Adding a table space

ALTER TABLESPACE users
ADD DATAFILE ‘/home/ssinghal/oradata/users01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M

Adding a table space with minimal options

ALTER TABLESPACE name ADD DATAFILE ‘filespec’ SIZE 200M;


Altering a tablespace by adding a new datafile

ALTER TABLESPACE VPN2_LRG_TBLS
ADD DATAFILE '/home/ssinghal/oradata/users02.dbf' SIZE 100M
AUTOEXTEND ON
NEXT 1024K
MAXSIZE 250M


Resizing a data-file

alter database datafile '/home/ssinghal/oradata/users02.dbf' resize 500M


Dropping a datafile

alter database datafile 20 offline drop

Note : ID of datafile can be obtained from dba_data_file

Oracle Procedure Source code from Dictionary

SELECT to_char(text)
FROM user_source
WHERE type IN ('PACKAGE' ,'PROCEDURE','TRIGGER')

Vim and Sqlplus

open the following file :
$ORACLE_HOME/sqlplus/admin/glogin.sql
Add following line :
DEFINE _EDITOR='gvim -c "set filetype=sql"'

Typing edit will now open your favorite editor