:> 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
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
