Little Things...

LITTLE THINGS EVERY DBA SHOULD KNOW!

How to find the tablespace of a table?
SELECT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';
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.

Find total number of records in a table

The simple query to find the total number of records is

SELECT COUNT(*) FROM tablename;

If you want to see the record count of more than one table, you may

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';

if the tables are ANALYZED.

Or you may create a script quickly by

SET PAGES 0 FEEDBACK OFF ECHO OFF VERIFY OFF TERMOUT OFF
SPOOL COUNT.SQL
SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';
SPOOL OFF
SET TERMOUT ON
@COUNT.SQL
SET FEEDBACK ON VERIFY ON PAGES 24

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');
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.

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".

What is the SGA size?

There are two simple ways to find this. The first is to invoke server manager (svrmgrl) and connect ineternal (or as any user). Issue the command "SHOW SGA". Or you can run the query "SELECT * FROM V$SGA;" from svrmgrl or sqlplus.

Where are my alert log and dump files written to?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK".

How to create structure (no data) of a table from another table?

If you need to duplicate a table you can do a "create table newtablename as select * from tablename;" This will create the new table will all the data. If you need to create only the structure, add a where condition "1=2" or some condition which is always false.

CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME WHERE 1=2;
How to increase the size of a tablespace?

The size of the tablespace is increased by changing the size of the size of the underlying physical files. You can either add more space to the existing file by

ALTER DATABASE DATAFILE 'filename' RESIZE nn M;

OR you can add more physical datafiles to the tablespace by

ALTER TABLESPACE tablespacename
ADD DATAFILE 'filename' SIZE nn M;

Make sure you specify the full path name for the filename. Use the script tsinfo.sql to find the size and related physical files of a tablespace.

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".

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.