Oracle Snippets

Posted on by andres

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! 😉

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

2. Escape single quotes and ampersands on strings

Single quotes:

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

Ampersands:

SET DEFINE OFF;

3. Adjust page width and size

set pagesize 1000
set linesize 100

4. Truncate a table

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

truncate table mytable;

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

spool file.log
-- some commands here...
spool off

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

@/path/to/script.sql

7. Print text – like a comment

PROMPT text to print;

8. Check if a table exists

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

 

if-then-else

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

 

declare-begin-end

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

declare
    myCount integer;
begin
    select count(1) into myCount from aTable where aField = 1;
    if myCount = 0 then
        insert into tableName values(1, 'a');
    else
        delete from tableName where id = 1;
        insert into tableName values(1, 'b');
    end if;
end;

 

Working with remote databases (db links)

 

Create a link to a remote server

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

Drop a link to a remote server

drop database link

 

Execute a “select” on a remote server

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

 

Create a local copy of a remote table

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

 

Append data to a local table created from a remote table

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

 

Get a list of database links

select * 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.

SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

More to come…

Leave a Reply