ADD/Remove UNDO tablespace in oracle database

ADD/Remove UNDO tablespace in oracle database:-
=====================================

 every database having default undo tablespace, if you want to change it do follow below procedure.

1. first create new tablespace with new(as you want to create)
Example:-
CREATE UNDO TABLESPACE <NEW_NAME> DATAFILE '<datafile_location/name.dbf>' SIZE 1000M AUTOEXTEND OFF ONLINE RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

ALTER SYSTEM SET UNDO_TABLESPACE = <NEW_NAME>;
ALTER SYSTEM SET UNDO_RETENTION = <SET as per NEED>;

Once new one created, if you want drop old one follow steps below.
check any session already using old one  with below sql

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

you will get SID&Serial# if any in use, kill them if have or else you can proceed to drop old one.

To kill
SQL> alter system kill session 'SID,SERIAL#';

To drop
SQL> drop tablespace undotbs1 including contents and datafiles;



please help to share your suggestions,  or  share your experience on same kind of issue in comment session.
  your sharing or suggestions will helps me to improve in my future posts.

Thanks for the visit!!!

ADD/Remove TEMP tablespace in oracle database

ADD/Remove TEMP tablespace in oracle database:-
=====================================

  To add new Temp tablespace and change it as default do follow below procedure.

1. First create New temptablespace and change it to default temp.

CREATE TEMPORARY TABLESPACE <NEW_TEMP_NAME> TEMPFILE '<teampfile location.dbf' SIZE 1000M AUTOEXTEND OFF
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <NEW_TEMP_NAME>;


Remove the old temptable space , before remove you must check any session already in use or not.

to check use below query to get live session(SID&serial#)  those who use old temp.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

THEN kill session if any session in use.

alter system kill session 'SID_NUMBER, SERIAL#';

we can drop old temporary tablespace.
DROP TABLESPACE TEMP including contents and datafiles;


Be careful while doing this in production.

Thanks for your visit my blog, if you have any suggestion or question please comment below. 

SQL write Query with Tuning tips

Query Performance Tuning
====================

 In this post i will give few tips while writing sql query, how we can write better query with less cost
 If you follow below steps keep in mind while writing query you can tune up your sql while writning
 time. Below are some tips which can be used as guideline.

  Make sure am not expert, But what i learn have been post here to help some one. 
  If you know more points or you want to add on more please post in comment session it help to me and also others to learn. 
  Even though i can miss some silly points or known point as well. 
  so please help to remind them !!!

1. DISTINCT:-  Do not use, If the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.  
2. Indexed columns:- Do not modify indexed columns with functions like RTRIM, TO_CHAR, UPPER, TRUNC, As this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function, consider creating a function based index. (many cases people use and functions and complain my query not using index).
3. UNION:- Do not use UNION, if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.
4. EXISTS :- use if selective predicate in parent query .
5. IN : use if selective predicate is in the sub query.
6. joins from one complex view to another not recommended.
7. Do not use HINTS (as oracle new version using CBO(cost based optimizer).
8. statistics for the objects used in the query are up to date or not, need to check.
9. Read explain plan and try  tune your query.
10. Commented lines are not good in between query lines.
11. Instead of using NULL some cases DECODE better to use.

still have more point will add on later .

Thanks for your visit, please help to post in comment session if you have more points or any correction from my statements. it help to me &others!!!