LITTLE THINGS EVERY DBA SHOULD KNOW!

SELECT tablespace_name

FROM all_tables

WHERE table_name = 'YOURTABLENAME';

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.

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.

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

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

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.

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

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.

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

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;

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.

This can be found by invoking server manager (svrmgrl) and issuing the command "ARCHIVE LOG LIST".