Tag Archives: ORACLE

ORACLE : Table Space Tricks!

tablespace First a brief introduction :

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.
Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.
Lets get a bit Techincal – What is a DB_File :  DB_FILE is the physical file that is created to store the data in Oracle.  The db_files parameter is a “soft limit ” parameter that controls the maximum number of physical OS files that can map to an Oracle instance. Historically (before Oracle8i) you need to be careful not to set db_files too high, else you would have DBWR (database writer) issues. The maxdatafiles parameter is a different “hard limit” parameter.  When you issue a “create database” command, the value you specify for maxdatafiles is stored in your Oracle control files.  The default value of 32 is usually sufficient, but after Oracle8i there is no downside to using a larger value.
Issues – How to incerease table space when you run out :
1. Check for current db_file size –
Show parameter db_files;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_files                             integer     100

2. Check if you have spfile by using the SQL command – show parameter spfile;
if you dont have spfile create one using the SQL command – create spfile from pfile;
3. Shutdown immidate; and startup mount;
4. alter system set db_files=750 scope=spfile; ( to incerease the db_file size )
5. Shutdown immidate; and startup mount;

Leave a comment

Filed under ORACLE

ORACLE : who is connected to My Oracle Instance

spy You have a DB you call your own, but you cant seem to drop some users eventhough you have stopped all services you were running which might have connected to your Oracle Instnace,

Well they (Apps or users) can’t hide any longer … The Oracle’s best Spy is on your side – here is the SQL which can bring them to the open. ( you need to be the SysDBA to do this though )

1. set linesize 30000 – to make sure everything appears in a single line

2. SQL> select username, sid, serial#, status, osuser, process, machine, terminal, program, logon_time from v$session where username is not null and username not in (‘SYSTEM’,’SYS’,’DBSNMP’);

– All the agents are displayed in a table with their USERNAME, SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM
you have to decide if you will shoot (force kill ) or negotiate ( stop the services in the machines they are running in )

1 Comment

Filed under DB, ORACLE

ORACLE : How to DROP a User who is connected – forcefully

cannon The ideal way for all development shops to work is to have each developer have his own database, but as you all know life is not that easy on us!.

So you want to update the schema, which requires to drop all users and you see that your script is complaining that some of the users (in oracle users are the schemas) are still conneted and cannot be dropped. This   would normally mean some one is running an application/service/App server, which is holding some connections to your database.

You have been nice to all, mailed the group, pinged some of your fellow developers, but there is a rogue application holding on to a user/schema. What would you do.  wait for ever or take matter into your hands

Here is some Mojo and fire power for you to boot/kill those users force fully ( ofcourse you need to have SysDba access to the database, otherwise you are out of luck )

  • find the sessions for the users/schema , as a DBA use : select sid,serial# from v$session where username = '<user_schema>'
  • Then kill them with : alter system kill session '<sid>,<serial#>'

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

This will return one kill statement per session for that user – something like:

alter system kill session '375,64855';

alter system kill session '346,53146';

  • Now Drop the user : drop user <user_schema_name> cascade;

That’s it – The User is vaporized !


Filed under DB, ORACLE