Kill oracle session in OS LEVEL

Sometimes, “ALTER SYSTEM KILL SESSION” will not terminate a session, it will just mark it as “killed”. In this case you can kill the process or thread of that session at the operation system level.

In Windows

C:\ ORAKILL ORACLE_INSTANCE THREAD;

THREAD means SPID of the process .

We can simply find out THREAD(SPID) By below query.
select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
Identify SPID of the particular user which you want to Kill

SQL> select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;

Example

SQL> select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr;

SPID OSUSER PROGRAM
------------------------ ------------------------------ --------------------
8876 NICE JDBC Thin Client

In this above Example THREAD(SPID) is 8876

SQL> Select instance_name from v$instance;
INSTANCE_NAME
-------------
ORCL
C:\ ORAKILL ORCL 8876;

To kill the session on the Unix operatin system, you can use “kill” command:

KILL THREAD

In the above Example

Example : KILL 8876

If after a few minutes the process hasn’t stopped, you can force the session to terminate by using “-9″ parameter:
kill -9 THREAD
Example : KILL -9 8876
Note: Make sure that you are not killing background processes of Oracle such as DBWR, LGWR, SMON, PMON. If you kill one of them, Oracle will crash or become unstable

Checking Invalid Objects in a Schema

You can identify invalid Objects in your schema by using below query.

select * from user_objects where status='INVALID'

You can compile invalid objects in a schema one by one.

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OBJECT_NAME || ' COMPILE;'
from
user_objects
where
status = 'INVALID';


Otherwise you can Compile all the objects in a schema


EXEC DBMS_UTILITY.compile_schema('Username');

ora-0155 snapshot too old error

Cause:Due to Oracle Flashback operations, Database is not able to dynamically tune the undo retention period effectively.

Action:

1)You can include CONSISTENT=Y parameter as in the case of Export(EXP).
2)Try to increase the value of UNDO_RETENTION parameter to match the length of the longest Oracle Flashback operation.
3)Schedule long running queries after your required operation.
4)Increase size of UNDO tablespace.

Cheers!!!

ORA-20185: operation disallowed on workspace 'WORKSPACE_NAME' involved in a conflict resolution session.

BEGIN DBMS_WM.removeworkspace('WORKSPACE_NAME'); END;
Error at line 3
ORA-20185: operation disallowed on workspace 'WORKSPACE_NAME' involved in a conflict resolution session
ORA-06512: at "WMSYS.LT", line 4955
ORA-06512: at line 1

Cause:Conflict on workspace.

Action:

SQL> exec dbms_wm.SetConflictWorkspace('WORKSPACE_NAME');

SQL> exec dbms_wm.commitresolve('WORKSPACE_NAME');

SQL> exec DBMS_WM.removeworkspace('WORKSPACE_NAME');

Workspace Removed


Cheers!!!

Monitoring Memory Resizing Operations with Memory_target Parameter.

To enable memory target

SQL> alter system set memory_max_target=5G scope=spfile;

SQL> alter system set memory_target=4G scope=spfile;

Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET
parameters by issuing the following ALTER SYSTEM commands:

SQL> alter system set sga_target = 0;

SQL> alter system set pga_aggregate_target = 0;

SQL>shutdown immediate;

SQL>startup


Under an automatic memory management mode, you can view the current allocations of
memory inany instance by querying the V$MEMORY_DYNAMIC_COMPONENTS
view. Querying this view provides vitalinformation to help you tune the MEMORY_TARGET parameter. Here’s how you execute a query against thisview:

SQL> select * from v$memory_target_advice order by memory_size;

Your current memory allocation is shown by the row with the MEMORY_SIZE_FACTOR value of 1 . The MEMORY_SIZE_FACTOR column shows alternate sizes of theMEMORY_TARGET
parameteras a multiple of the current MEMORY_TARGET parameter value. The
ESTD_DB_TIME column shows the timeOracle estimates it will need to complete the current workload with a specific MEMORY_TARGET value. Thus, the query results show you how much faster the database can process its work by varying the value of the
MEMORY_TARGET parameter.

ORA-04030: out of process memory while trying to allocate "N" Bytes

Cause:Oracle cannot perform requested operation with this free memory.

Action:

1)Tune pga_aggregate_target value depends on your server memory.
2)Server is windows,Increase your page file size with the help of system admin.
If you are working with UNIX platform check kernal parameters of shared memory .
3)Move to multi-threaded server.



Cheers!!!

Reclaiming Unused Index Space

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.