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

[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