LITTLE THINGS EVERY DBA SHOULD KNOW!
The records can be identified and removed using the query
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
If you want to see the record count of more than one table, you may
if the tables are ANALYZED.
Or you may create a script quickly by
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
Using the above method you find the SID and SERIAL# for the session you wish to terminate. Then issue the command
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
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.
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
OR you can add more physical datafiles to the tablespace by
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".