avatar

Andres Jaimes

Oracle Snippets

By Andres Jaimes

This post will be updated with snippets for Oracle. I hope you find it useful.

 

1. Change the default date format

Most useful snippet of the year! 😉

1ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
2select sysdate from dual;

2. Escape single quotes and ampersands on strings

Single quotes:

1select 'D''Angelo''s' from dual;

Ampersands:

1SET DEFINE OFF;

3. Adjust page width and size

1set pagesize 1000
2set linesize 100

4. Truncate a table

Be careful: You cannot roll back a TRUNCATE TABLE statement.

1truncate table mytable;

5. Send all output from sqlplus to an external log file

1spool file.log
2-- some commands here...
3spool off

6. Load and execute in sqlplus a script from an external file

1@/path/to/script.sql

7. Print text – like a comment

1PROMPT text to print;

8. Check if a table exists

1select count(*) into v from all_tables where table_name='YOUR_TABLE';

 

if-then-else

1if myCount = 0 then
2    insert into tableName values(1, 'a');
3else
4    delete from tableName where id = 1;
5    insert into tableName values(1, 'b');
6end if;

 

declare-begin-end

You can declare as many vars as you need in the declare section.

 1declare
 2    myCount integer;
 3begin
 4    select count(1) into myCount from aTable where aField = 1;
 5    if myCount = 0 then
 6        insert into tableName values(1, 'a');
 7    else
 8        delete from tableName where id = 1;
 9        insert into tableName values(1, 'b');
10    end if;
11end;

 

 

1create database link <my-connection-alias> connect to <my-user> identified by <my-password> using '<server:port/sid>';

Drop a link to a remote server

1drop database link

 

Execute a “select” on a remote server

1select count(*) from <my-remote-table>@<my-connection-alias>;

 

Create a local copy of a remote table

1create table <my-local-table> as select * from <my-remote-table>@<my-connection-alias>;

 

Append data to a local table created from a remote table

1insert into <my-local-table> * from <my-remote-table>@<my-connection-alias> where...;

 

1select * from ALL_DB_LINKS;

You can choose from here:

  • DBA_DB_LINKS: All db links in the database
  • ALL_DB_LINKS: All db links the current user has access to
  • USER_DB_LINKS: All db links owned by the current user

 

SQL Developer

Autoformat your SQL code:

ctrl + F7

Autoformat SQL code for other languages:

ctrl + shift + F7

Add a TNS file:

  • Go to Tools > Preferences > Database > Advanced > Tnsnames Directory

 

Oracle XE

Common installation directory

/u01/app/oracle

Size of tablespaces

Oracle XE 11g has a space limit of 11Gb for the Users tablespace (all others do not add up to this total). If your tablespace for Users says something like 99% of 300Mb, don’t worry, it will keep growing until it reaches its max limit.

 1SELECT /* + RULE */  df.tablespace_name "Tablespace",
 2  df.bytes / (1024 * 1024) "Size (MB)",
 3  SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
 4  Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
 5  Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
 6FROM dba_free_space fs,
 7  (SELECT tablespace_name,SUM(bytes) bytes
 8   FROM dba_data_files
 9   GROUP BY tablespace_name) df
10WHERE fs.tablespace_name (+)  = df.tablespace_name
11GROUP BY df.tablespace_name,df.bytes
12UNION ALL
13SELECT /* + RULE */ df.tablespace_name tspace,
14  fs.bytes / (1024 * 1024),
15  SUM(df.bytes_free) / (1024 * 1024),
16  Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
17  Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
18FROM dba_temp_files fs,
19  (SELECT tablespace_name,bytes_free,bytes_used
20   FROM v$temp_space_header
21   GROUP BY tablespace_name,bytes_free,bytes_used) df
22WHERE fs.tablespace_name (+)  = df.tablespace_name
23GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
24ORDER BY 4 DESC;