Skip to main content

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.

Comments

Post a Comment

Popular posts from this blog

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_...
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; ce...
Post a Comment
Read more

RMAN Different errors and their Solution

RMAN Different errors and their Solution Backup Fails with Invalid RECID Error: RMAN-20035, RMAN-6038 When you attempt a backup and receive the following error messages: RMAN-3014: Implicit resync of recovery catalog failed RMAN-6038: Recovery catalog package detected an error RMAN-20035: Invalid high RECID error It indicates the control file and the recovery catalog is not synchronized. RMAN detects that the control file currently in use is older than the control file previously used to resynchronize. Cause: This due to any of the scenario you are restore a backup controlfile through a non-oracle mechanism and then open the database through Resetlogs option or making a copy of control file through operating system utility and trying the restore on new system through RNAN. You do not use catalog so RMAN does not get any information regarding this process.  The recovery catalog indicates that the highest RECID is 100, but the control file indicates that the highe...
Post a Comment
Read more