Oracle Tips

Alter date format

Oracle's default date format is YYYY-MM-DD, if you want to change it, you could use the following:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 

Non-ASCII characters

Pay attention to non-ascii characters when you insert data. Because each of them ocuupies two bytes. Consider the following

Grandfather‘s Hut

The string needs 18 bytes instead of 17 (pay attention to '‘'), so you must ensure your columns size is enough.



Find RAC sessions

select * from v$session;

or

select * from gv$session;

You can find the users connecting to each node if you query the v$session. You must be aware of the node you are connecting when issuing this query because the information is exclusive from the node you are connected to.

If you query the gv$session the information gathered is the same, but as you can see it reports information from all RAC participant users.

If you take a look at the dynamic views, you will find a lot of gv$ views, those are global views that can be seen from whichever instance you are connected to and it will report information about all the instances in a single query. As you can see the gv$ views include an INST_ID (instance ID) column, which declares the number of instance the information comes from.

Find All Uniques Indexes

select * from all_indexes where index_name='table_name$unique_index_name';

select * from all_ind_columns where index_name='table_name$unique_index_name'; 

Unique indexes are stored in the table all_indexes instead of all_constraints since they are not only constraints but also indexes.