Some of the Useful SQL Querise for DBA's and users.

Which database am I connected to? As which user?
The database name can be found out from different views. The view which everyone has access is GLOBAL_NAME. The query is

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

To find the user, from sqlplus you can do "SHOW USER".

How to terminate a session?
Using the above method you find the SID and SERIAL# for the session you wish to terminate. Then issue the command

ALTER SYSTEM KILL SESSION 'sid, serial#';

Please note that the sid and serial# should be in quotes separated by a comma.

SET SQLPROMPT 'MYNAME> '

It can be set dynamically by adding code to a login.sql file in the executable path.
This is what I use:

SET FEEDBACK OFF
SET TERMOUT OFF

COLUMN X NEW_VALUE Y
SELECT LOWER(USER || '@' || instance_name) X FROM v$instance;
SET SQLPROMPT '&Y> '

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

SET TERMOUT ON
SET FEEDBACK ON
SET LINESIZE 100

This script is only called at connect time in 10g, or startup time in 9i, so it's not dynamic, like when the statement has run. If you wnt the time in addition to your custom SQLPROMPT you need to do this: SET TIME ON


How to remove duplicate rows from a table?
If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:

DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);

Here column1, column2, column3 constitute the identifying key for each record.
If the keys cannot be identified for the table, you may create a temporary table using the query

CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;

Then drop the original table and rename the temp table to original tablename.

How to identify and remove bad 'child' records to enable / create a foreign key ("Parent Keys Not Found" error when you try to enable/create relation from child table to parent table!)
The records can be identified and removed using the query

DELETE FROM childtablename ct
WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt
WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...)

Or if you need to provide the user with bad records you may change the DELETE to SELECT with column list.

Is my database running in Archivelog mode? Where are the archived files written?
This can be found by invoking server manager (svrmgrl) and issuing the command

"ARCHIVE LOG LIST".

What is my current session id?
The username, program, machine, terminal, session id, serial # and more can be found from the v$session view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session. The query could be

SELECT USERNAME, SID, SERIAL#, PROGRAM FROM V$SESSION
WHERE AUDSID = USERENV('SESSOINID');