Oracle Migration Table Space

Source: Internet
Author: User

1. Create a new tablespace newspaces (original tablespace oldspaces)

2. Export data from the original tablespace (TEST.DMP), then import the previously exported data (TEST.DMP) in the new tablespace, and select Import from user to user

3. Execute the following sql:

Select ' ALTER TABLE ' | | table_name | | ' Move tablespace target tablespace name; ' From User_all_tables

Export the result set and copy all the SQL from the query to plsql inside the exported file.

Description: This table is migrated and completed, but since the index is involved in the table, we must also migrate the index pages before migrating the index, because there are many large segments (LOB) types in the index, and you need to work with large fields first.

4. Handling Large Print Segments

Execute the following SQL to query the table that has large fields

Select Ui.table_name from User_indexes UI where ui.index_type= ' LOB '

Next you need to open a table with large fields to find columns with large fields

Put the name of the table, and the column names of large fields in the following SQL execution (the same table, you can put all the column names in one SQL execution, multiple tables, create multiple SQL to handle):

ALTER TABLE indicates the move tablespace govhrdb LOB (column name 1, column name 2) STORE as (tablespace YWJ);

You can also use the following statement to automate query generation:

select ' ALTER TABLE ' | | | tsb.table_name | | ' Move tablespace ' | |
TSB. Tablespace_name | | ' LOB (' | | | tsb.col | |
') STORE as (tablespace migrated to tablespace name ); '
From (SELECT table_name,
LTrim (Sys_connect_by_path (column_name, ', '), ', ') as Col,
Tablespace_name
From (SELECT table_name,
column_name,
Tablespace_name,
row_number () over (partition by table_name ORDER BY column_name) RN,
COUNT (*) over (partition by table_name) CNT
From (SELECT cols.table_name,
Cols.column_name,
Cols.data_type,
DD. Tablespace_name
From all_tab_cols cols
Left join All_tables DD
On dd.table_name = Cols.table_name
WHERE Cols.table_name in
(select Ui.table_name
From user_indexes UI
where ui.index_type = ' LOB ')
and data_type like '%lob ')) A
where level = CNT
start with RN = 1
Connect by PRIOR TABLE_NAME = table_name
and Prior RN = rn-1) TSB;

5. Migrating basic types of indexes

Execute the following SQL

Select ' Alter index ' | | index_name | | ' Rebuild tablespace target tablespace name; ' From user_indexes

Export the result set, and then in the exported file inside the query stitching SQL all copied to plsql inside execution, if you encounter a prompt to skip directly.

6. Data export

Because when the index is migrated, the impact on the index value, if the use of ordinary exp command export, the next time the import error, for this issue, use the following command to export data, so that the data consistency, re-import the time there will be no error

Export command: EXP username/password @ database name file= Export file path consistent=y

Consistent=y means keeping the file intact

Note: only need to export the first time after the migration using the above command to export, after the data import, export does not follow the normal steps, do not need to add consistent=y if you encounter import index error, reference partition data import error problem solution

When Oracle imports data, there are sometimes problems with partitioning data that cannot be imported, as well as the potential risk of an index state of ' unusable ' when migrating table spaces, as a solution:

--Query out all indexes with status unusable and generate SQL
Select ' Alter index ' | | index_name | | ' Rebuild; ' from user_indexes where status= ' unusable ';
--Example:
Alter index pk_un_organization_function rebuild;
Alter index PK_OA_NOTICE rebuild;
Alter index ak_key_2_core_mes rebuild;
Alter index PK_CORE_MESSAGE_POOL rebuild;
Alter index AK_KEY_2_CORE_LOG rebuild;
Alter index PK_CORE_LOG rebuild;
Alter index pk_un_organization_basis rebuild;
Alter index PK_OA_ORGCONFIG rebuild;

Execute the SQL statement generated by the query to

Oracle Migration Table Space

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.