Oracle Date Display Format

To change the format that a date is displayed in Oracle use the set NLS_DATE_FORMAT command. For example, if you wanted to show hours minutes and seconds with a date time stamp you could use: -

alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI'

before executing your query.

Creating a Database Link

I always forget the syntax for this one...

CREATE DATABASE LINK 
CONNECT TO 
IDENTIFIED BY 
USING '';

For example: -

CREATE DATABASE LINK HR89TST
CONNECT TO FBLOGGS
IDENTIFIED BY FR3DD13
USING 'HR89TST';

Note that if you have special characters in your password, you should enclose it in double quotes or you may get an error message.

To list existing database links, use: -

select * from dba_db_links;
or

Select * from ALL_DB_LINKS;

To drop a database link use: -

DROP DATABASE LINK linkname;

Oracle 11g Release on You Tube

Blogged with Flock

Analyze This

In the good old days you analyzed a table using a statement along the lines of: -

ANALYZE TABLE tablename COMPUTE STATISTICS;

With the advent of Oracle 8i the DBMS_STATS utility was introduced to gather object statistics. This is Oracles preffered method of gathering the information.

Using it the equivalent of the above analyze statement would be: -

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE);

This should be executed after the table you need to analyze has been populated.

To cut back on the time it takes to execute, you can limit the number of rows it uses in its stats gathering by adding estimate_percent => nn (where nn is a number between 1 and 100) this will only use the given percentage of the total rows in the table when performing its anaysis.

e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE, estimate_percent => 20);

Will only use 20% of the tables rows. The higher the percentage, the better the analysis, but the longer it will take. When used Oracle randomly selects the rows it uses from the table to make them more representative.

N.B. This statement doesn't work from Oracle Developer (I've not tried it from Toad). As such execute it from SQL Plus instead.

Useful SQL Plus Settings

On occaisions when you're working at an Oracle site with no access to sensible SQL development tools, such as Toad or Oracle Developer (available as a free download from the Oracle website, but no good if you can't download or install it), you are forced to use that most heinous of applications SQL Plus. To make life a bit more palatable, try using the following settings to brighten up its drab and dreary demeanor.

/* SQL Plus parameters required for each session */
select name from sys.v_$database;
alter session set current_schema=SYSADM;
set timing on;
set sqlprompt dbasename> ;
set linesize 150;
set null empty;
set pagesize 500;
set pause on;
set space 1;
set time on;
set trim on;
set wrap off;
set sqlnumber on;
set describe line on;

Enjoy !!!!

Finding The Current Database Name

Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.

Oracle
In Oracle you can get the current database name by querying the table v_$database

i.e.

select name
 from sys.v_$database

SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.

e.g.

select DB_Name()

Set Current Schema

I always forget the syntax for setting the current schema in Oracle. Then again that's why the great Flying Spaghetti Monster created blogs.

alter session set current_schema = sysadm