[ORACLE] production environment using data pump impd, expdp for data migration tutorials, impdexpdp

Source: Internet
Author: User

[ORACLE] production environment using data pump impd, expdp for data migration tutorials, impdexpdp

1. operations on the original database

1.1 view original configuration

1.1.1 view User Permissions

View user system permissions:

SQL> select * from user_sys_privs;

View User Roles

SQL> select * from user_role_privs;

View the permissions of the current session

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE VIEW

CREATE SEQUENCE

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

1.1.2 View table

View All tables under a user

SQL> select * from user_tables;

View the creation time of a table

Select object_name, created from user_objects

View the size of a table

Select sum (bytes)/(1024*1024) size_M, segment_name from user_segments group by segment_name;

View the table in the ORACLE memory Partition

SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0;

1.1.3 view and Index

View a user's View

Select * from user_views;

View users' Indexes

SQL> select * from user_indexes;

View the number and category of Indexes

SQL> select index_name, index_type, table_name from user_indexes order by table_name;

View indexed fields

SQL> select * from user_ind_columns where index_name = upper ('& index_name ');

View index size

SQL> select sum (bytes)/(1024*1024) as "size (M)" from user_segments

Where segment_name = upper ('& index_name ');

1.1.4 view stored procedures

View the status of functions and processes

SQL> select object_name, status from user_objects where object_type = 'function ';

SQL> select object_name, status from user_objects where object_type = 'Procedure ';

View the source code of functions and processes

SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

1.1.5 view the serial number

Select * from user_sequences;

1.1.6 view Synonyms

Select * from user_synonyms;

1.1.7 view the constraints of a table

SQL> select constraint_name, constraint_type, search_condition, r_constraint_name

2 from user_constraints

3 where table_name = 'family ';

1.2 data export

Create a Dump path

SQL> connect/as sysdba

Connected.

SQL> create directory dump_dir as '/oracle ';

SQL> grant read, write on directory dump_dir to u1;

You can export stored procedures, data, views, indexes, and so on based on user export.

Expdp system/oracle schemas = u1 dumpfile = 'u1. dump' directory = dump_dir version = 10.2.0.1.0

2. target database operations

2.1 create a tablespace and a user

SQL> create tablespace u1

2 logging

3 datafile '/home/oracle/oradata/db1/u1.dbf'

4 size 30 m

5 autoextend on

6 next 32 m

7. maxsize 100 m

8 extent management local;

SQL> create user u1 identified by tiger default tablespace u1;

Grant resource, connect to u1;

2.2 import by user

Create a Dump path

SQL> connect/as sysdba

Connected.

SQL> create directory dump_dir as '/oracle ';

SQL> grant read, write on directory dump_dir to u1;

Impdp system/oracle schemas = u1 dumpfile = 'u1. dump' directory = dump

3. Other parameters

3.1 export by version

[Oracle @ host2 ~] $ Expdp system/oracle tablespaces = u1 dumpfile = 'u1 _ version. dump 'directory = dump_dir version = 10.2.0.1.0

[Oracle @ host2 oracle] $ impdp system/oracle tablespaces = u1 directory = dump dumpfile = 'u1 _ version. dump 'version = 10.2.0.1.0

3.2 export Definitions

Expdp system/oracle tablespaces = u1 dumpfile = 'u1 _ all. dump' directory = dump_dir version = 10.2.0.1.0 CONTENT = ALL

3.3 export stored procedures only

PROCEDURE can also be replaced with other objects such as INDEX, TABLE, FUNCTION, VIEW, database link, SYNONYM, package body, SEQUENCE, LOB, PACKAGE, PROCEDURE

[Oracle @ host2 oracle] $ expdp system/oracle schemas = u1 dumpfile = 'u1 _ p. dump 'directory = dump_dir version = 10.2.0.1.0 include = PROCEDURE

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.