Creating and Handling Tablespaces

Creating a new Tablespace in Database:

Create Tablespace with  below command, mention

• Tablespace name
• data file path & name
• Size- Initial,,Extendable and Maximum Size of data file.

Initially a data file needs to be created to create a tablespace.

CREATE TABLESPACE <Tablespace_Name> DATAFILE <Path with data file Name>
    SIZE <Initial Size> REUSE AUTOEXTEND ON NEXT <Extentable value> MAXSIZE <Maximum size for this datafile>;

Example:

CREATE TABLESPACE ARMS_IDX DATAFILE 'D:\APP\ORADATA\ORCL\ARMS_IDX.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 700M;

After Creating a Tablespace, additional datafiles could be added to increase size of tablespace.

ALTER TABLESPACE ARMS_IDX add datafile 'D:\APP\ORADATA\ORCL\arms_IDX1.dbf'  SIZE 30G AUTOEXTEND ON;

Killing Multiple Session in Oracle database

Many Active sessions and Inactive sessions are responsible for

a. Long Running procedures/packages in a new session.
b. Locking of Tables and hence delaying any DML operations.
c. Slowness in database performance.

Steps to be followed to kill those sessions:

a. Kill those Active and InActive sessions using:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

b. Disconnecting the Active and InActive sessions;

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' (POST_TRANSACTION | IMMEDIATE);

POST transaction: Disconnects sessions after the transaction completes.

IMMEDIATE: Disconnects sessions immediately and rollbacks the changes of DML statements.

Note:
SID and SERIAL# from V$SESSION table.

If database is shared among more schemas, consider instance id(INST_ID) from GV$SESSION.
Then statement would be :

ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@inst_id';

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#,@inst_id' (POST_TRANSACTION | IMMEDIATE);


For killing mutiple sessions, use the below query:

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''';' FROM GV$SESSION WHERE STATUS='INACTIVE';

For Disconnecting Multiple sessions, use below query:

SELECT 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION WHERE STATUS='INACTIVE';

SELECT 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' POST_TRANSACTION ;' FROM GV$SESSION WHERE STATUS='INACTIVE';