Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 8 March 2015

How to change Database Default Tablespace for users

1. Create new Tablespace <KAFA_ITGFIN_DBF> for schema KAFITGFIN
create tablespace KAFA_ITGFIN_DBF datafile
'D:\ORACLE\ORA92\MUJAZORC\KAFA_ITGFIN_DBF.DBF' size 5120m autoextend on;
2. Take Export of schema KAFAFIN on Seperate drive.
EXP SYSTEM/SYSMAN@mujazorc OWNER=KAFAFIN FILE=F:\Dump\kafafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\kafafin_16022015.log
3. Now create new schema or if you have already existing schema then drop and recreate it.
drop KAFITGFIN CASCADE;

CREATE USER KAFAITGFIN
  IDENTIFIED BY VALUES KAFAITGFIN
  DEFAULT TABLESPACE KAFA_ITGFIN_DBF
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for KAFITGFIN
  GRANT DBA TO KAFAITGFIN WITH ADMIN OPTION;
  ALTER USER KAFAITGFIN DEFAULT ROLE ALL;
  -- 1 System Privilege for KAFAITGFIN
  GRANT UNLIMITED TABLESPACE TO KAFAITGFIN WITH ADMIN OPTION;
  -- 1 Tablespace Quota for KAFITGFIN
  ALTER USER KAFAITGFIN QUOTA UNLIMITED ON KAFA_ITGFIN_DBF;

alter user KAFAITGFIN quota 0 on KAFA_FIN_DBF quota unlimited on KAFA_ITGFIN_DBF;
alter user KAFAITGFIN quota 0 on MUJ_FIN_DBF quota unlimited on KAFA_ITGFIN_DBF;
Note: Here in above example two different tablespace having the tables of same users.

In the case while changing the default tablespace for the user's in the same database, don't forget to assign
quota unlimited on new tablespace and revoke unlimited quota on old tablespace. This might not required while changing the tablespace of user's of different database.

revoke unlimited tablespace from KAFA_ITGFIN_DBF;

4. Now try to import the dump you have taken. Make sure dump is properly copied on the drive.

IMP SYSTEM/sysman@MUJAZORC FILE=F:\Dump\kafafin_16022015.DMP FROMUSER=KAFAFIN TOUSER=AWAED LOG=F:\Dump\kafafin_16022015.DMP.log
Don't forget to assign back quota unlimited on 'awaed' tablespace.
alter user awaed DEFAULT tablespace awaed QUOTA UNLIMITED ON awaed;

How to Move Tables into new tablespace of another database:
The case when you need to change tablespaces which is wrongly assigned and you want to move those users tables to different tablespace. First identify the tables from database which you want to move:
Select owner,table_name,tablespace_name from dba_tables where owner = 'ORAFIN';
OWNER TABLE_NAME TABLESPACE_NAME
ORAFIN ACCFILE MUJ_FIN_DBF
ORAFIN ACCOUNT_TRN           MUJ_FIN_DBF
ORAFIN ACCOUNT_TRN1         MUJ_FIN_DBF
ORAFIN APPLICATION MUJ_FIN_DBF
ORAFIN ASSETS1 MUJ_FIN_DBF
ORAFIN ASSETS1_ALL MUJ_FIN_DBF
ORAFIN ASSETS_ALL         MUJ_FIN_DBF
ORAFIN ASSETS_ALL_HI         MUJ_FIN_DBF
ORAFIN AUT_DISC         MUJ_FIN_DBF
ORAFIN AUT_LABEL         MUJ_FIN_DBF

Now you can use alter table command to move that tables to new tableapace. In the case while
moving the tables having index then you need to rebuild the index otherwise no need to do any thing.

Alter table table_name move tablespace KAF_ITGFIN_DBF;
Alter index <index_name> rebuild tablespace new_tablespace_name;

Generally Normal index moved explicitly where as LOB indexes are moved implicityly where as
related constraint will automatically moved to the new tablespace.

Now make sure the new schema is having the new default tablespace or not. Then the next time object is created on new tablespace.

32 comments:

  1. Hi Shahid, very good article, thanks for providing in-depth information on Oracle DBA Technology. Please continue sharing.

    ReplyDelete
  2. Excellent post. Keep doing it

    ReplyDelete
  3. Nice Blog for Oracle apps DBA,we provide online training,On line job Support and Consulting
    eBiztechnics-Oracle apps Blog

    ReplyDelete
    Replies
    1. Nice Blog for Oracle apps DBA,we provide online training,On line job Support and Consulting
      eBiztechnic Oracle apps Blog

      Delete
  4. Latest Govt Bank Jobs 2016

    Good Post....... Keep sharing this kind of Inoformation

    ReplyDelete
  5. Rajasthan Patwari Recruitment 2015


    I want to thank you for this informative read; I really appreciate sharing this great.........

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. The fantastic information to be shared through in this post and then the i think this is very wonderful site one.
    I like this post.
    php training in chennai

    ReplyDelete
  8. Really awesome article you had posted and please keep update like this information with this site. UIts very useful

    SQL DBA Training in Chennai

    ReplyDelete
  9. Nice Article... Thanks for Sharing with us !!!

    Visit Below link to learn Oracle SQL & PLSQL-

    http://tekclasses.in/

    ReplyDelete
  10. thanks for sharing the useful information.ORACLE ONLINE COURESE

    ReplyDelete
  11. thanks for sharing valuable information on changing database your blog has a very good points.

    thanks for sharing.

    ReplyDelete
  12. Nice Blog for Oracle apps DBA,thank you for providing in-depth information on Oracle DBA Technology,
    thank you for sharing..

    ReplyDelete
  13. Awesome resources!
    Thanks for sharing such good article.

    Best job Consultancy in Bangalore

    ReplyDelete
  14. Great post.
    It really helped me to learn something new. So thanks.
    Online Political Campaigns In Bangalore

    ReplyDelete
  15. Shree Ram Techno Solutions Provides CCTV Camera, Security Camera, Wireless Security, Attendance System, Access Control System, DVR, NVR, Spy Camera, Fire Alarm, Security Alarm, PCI, IP Network Camera, Dome Camera, IR Camera, CCTV, Camera Price, HIKVISION, SCATI, Time Machine

    CCTV CAmera in jaipur at Rajasthan
    Home security system in jaipur
    Wireless Home Security System in jaipur
    Realtime attendance machine in jaipur
    cctv camera dealer in jaipur
    Hikvision DVR in jaipur at Rajasthan
    security system solutions in jaipur
    Shree Ram Techno Solutions Provides CCTV Camera, Security Camera, Wireless Security, Attendance System, Access Control System, DVR, NVR, Spy Camera, Fire Alarm

    ReplyDelete
  16. Awesome resources!
    Thanks for sharing such good article. This is really helpful.
    cladding tiles
    Kitchen Tiles
    Highlighter Tiles

    ReplyDelete
  17. Thanks for sharing the very useful info about Oracle and please keep updating........

    ReplyDelete
  18. A pioneer Institute owned by industry professionals to impart vibrant, innovative and global education in the field of Hospitality to bridge the gap of 40 lakh job vacancies in the Hospitality sector. The Institute is contributing to the creation of knowledge and offer quality program to equip students with skills to face the global market concerted effort by dedicated faculties, providing best learning environment in fulfilling the ambition to become a Leading Institute in India.

    cha jaipur
    management college in jaipur
    management of hospitality administration jaipur
    cha management jaipur
    Best hotel college in jaipur
    Best management college in jaipur
    College of Hospitality Administration, Jaipur



    ReplyDelete
  19. Nice effort, very informative, this will help me to complete my task. Thanks for sharing it.Have a look at the process blogs to see more.


    Oracle Fusion financial

    ReplyDelete