Skip to main content

How to change Database Default Tablespace for users


1. Create new Tablespace <SHAH_SHAKIR_DBF> for schema SHAKIR

 create tablespace SHAH_SHAKIR_DBF datafile
'D:\ORACLE\ORA92\MUJAZORC\KAFA_ITGFIN_DBF.DBF' size 5120m autoextend on;

 2. Take Export of schema SHAKIR on Seperate drive:
EXP SYSTEM/SYSMAN@mujazorc OWNER=SHAKIR FILE=F:\Dump\kafafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\shakir_16022015.log

 3. Now create new schema or if you have already existing schema then drop and recreate it:
drop SHAKIR CASCADE;

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

alter user SHAKIR quota 0 on SHAKIR_FIN_DBF quota unlimited on SHAH_ITGFIN_DBF;
alter user SHAKIR quota 0 on SHAH_FIN_DBF quota unlimited on SHAH_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 SHAH_ITGFIN_DBF;

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

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

Comments

Post a Comment

Popular posts from this blog

Tablespace Management using Toad

You can view all of your tablespace information using toad tablespace screen from Database –> Administer –> Tablespaces Here each table describes different detailed information across all the tablespaces in database. If you open this screen from the toad DBA module then you are also able to see the “space history” and “IO history” tab along with Files, Free space, object, Fragmentation. With these tab you are able to able perform space monitoring and planning steps. These tab permits you check graphically space and IO usage over time. Through this way DBA are also able to estimate ‘Object size’ based on this estimation. From the below “alter tablespace” tab you can §          Modify the tablespace and datafile size. §          Add new datafile to the tablespace.
Post a Comment
Read more

Import Excel Data into Oracle using Oracle form Button

Import Excel Data into Oracle using Oracle form Button 1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger. BEGIN IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN   Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE'); END IF; IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN   IMPORT_EXCEL_PROC;   ---Form procedure ELSE  MESSAGE('Import allowed only for new entry!!!');  MESSAGE('Import allowed only for new entry!!!'); END IF; END; 2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC PROCEDURE IMPORT_EXCEL_PROC IS application    OLE2.Obj_Type; workbooks      OLE2.Obj_Type; workbook       OLE2.Obj_Type; worksheets      OLE2.Obj_Type; worksheet       OLE2.Obj_Type; cell      
Post a Comment
Read more

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_value fro
Post a Comment
Read more