|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
SELECT ‘SELECT COUNT(*) FROM ‘ || TABLE_NAME || ‘;’
WHERE TABLE_NAME LIKE ‘searchstring’;
SET TERMOUT ON
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“.