Oracle Tips - DML

1. Connecting through sqlplus from console

:> sqlplus user_name/user_password@database_name (fully qualified name as in tns.ora)

2. To connect to another user while already connected to some other user:

sqlplus> conn user_name/user_password@database_name (fully qualified as in tns.ora)

3. To see the currnet schema name that you're connected to:

select sys_context( 'userenv', 'current_schema' ) from dual

4. To see all the table names of the current user:

select table_name from user_tables;

5. To see the constraints of a table:

select table_name, constraint_name, constraint_type from user_constraints;

6. All table and column names of particular schema

select b.table_name, a.column_name from user_tab_columns a, all_tables b where b.owner = 'SCHEMA_NAME'
order by b.table_name

7. Convert row values into columns

Converting row to column is only possible using the below approach when you've predetermined values in the rows.

Approach-1: using case...when...then statement

select column_name1, column_name2,
max((case when column1 = 1
then value_col end)) as "user_defined_name1",
max((case when column1 = 2
then value end)) as "user_defined_name2"
From
Table_name
[group by column_name1, column_name2]


Approach-2: using left outer join:

select column_name1, column_name2,
t1.column1 as 'user-defined-name1',
t2.column1 as 'user-defined-name2'
from
table_name t1,
able_name t2
where
t1.pk = t2.pk
and t1.column1 (+) = 1
and t2.column1 (+) = 2


6. Update column

Apparently it seems harmless of the below update statement -

update table_name t set column_name =
(select column_name from another_table_name a where t.id = a.id and ....)

The problem is, each and every records would be updated even though the subquery returns few matches. The unmatched records in table_name would be updated with null. So there would be unwanted update that might not be expected. Moreover if the table_name has 10 million records, it would take humongous amount of time if you don't care about that null update.

The similar update can be achieved without getting updated with null for the unmatched records in table_name is -

update (select t.column_name, a.column_name from table_name t, another_table_name a
where t.id = a.id and ....) set t.column_name = a.column_name

The precondition is, the t.id and a.id must have unique constraints enabled or they're primary key in the corresponding tables


7. Check out if any object is currently locked out

select object_name, o.object_id from user_objects o, v$locked_object l
where o.object_id = l.object_id

8. Insert special characters like '&'

Set Define Off;
insert statement e.g. insert 'abc&d' into table_name (col1);
Set Define On;

9. See the comments on column

select table_name,column_name,comments from user_col_comments

10. Print the time difference inside a pl/sql

start_time = dbms_utlility.get_time;
// process data
end_time = dbms_utility.get_time;
dbms_output.put_line('time elapsed: ' || (end_time - start_time)/100 || ' secs');

11. SQL*PLUS options:
SQL> set timing on -- shows the elapsed time at the end
SQL>

12. Custom Order:

Use DECODE function in the order by clause instead of the column directly
e.g. Order By DECODE(column_name, 'AA', 1, 'A', 2, 'BB', 3, 'B',4, 'CC', 5, 'C', 6, 'DD', 7, 'D', 8, 'EE',, 'E',10)

13. Connect By:

TBD

14. using 'WITH':

TBD

15. prompt for variable substitution:

Ampersand (&) is used to prompt for the variable substitution. Also ACCEPT can be used to prompt for the customized prompt text e.g.

ACCEPT variable CHAR PROMPT 'Enter Variable: '



HIDE option can be added at the end to not to echo the entered password, for example.

16. Run Unix commands on the SQL prompot

use HOST before the Unix command
e.g.
SQL>HOST ls
SQL>HOST pwd

17. Explain Plan
use SYS.PLAN_TABLE$ in case you don't have privilege to create a plan table

Resources:
https://www1.columbia.edu/sec/acis/dba/oracle/faq-sql.html

http://www.dba-oracle.com/ - has useful queries

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969054700346631425 - Bulk move

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:273215737113 - Update columns from other table

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:974699100346868838 - should we use db user or application user?

http://www.adp-gmbh.ch/ora/misc/integrity_constraints.html - Oracle's Integrity Constraints

http://jenniferlinca.wordpress.com/ - Blog contains good Oracle stuffs

http://forums.oracle.com/forums/message.jspa?messageID=2348825 - Fun to read

http://www.dba-oracle.com/art_9i_indexing.htm - Performance tuning with index

http://www.orafaq.com/node/855 - Common errors using Outer Join

http://marist89.blogspot.com/ - Wonderful quote from the site - "The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt." --Bertrand Russell. A good database blog

http://www.orafaq.com/forum/t/54714/0/ - Batch delete

http://forums.oracle.com/forums/thread.jspa?threadID=620843 - custom order by

http://www.sqlsnippets.com/en/topic-12186.html - Convert Rows to Columns

http://www.dba-oracle.com/t_converting_rows_columns.htm - Convert Rows to Columns

http://www.orafaq.com/node/855 - Error prone Join