Database outage Migration oracle10g instances

1 operation of the original database 1.1 View Legacy Configurations 1.1.1 viewing user rights

To view user system permissions:

Sql> select * from User_sys_privs;

View User-owned roles

Sql> select * from User_role_privs;

View permissions for 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 the tables under the user

Sql>select * from User_tables;

To 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 tables placed in Oracle's memory area

Sql> Select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;

1.1.3 View View, index

View a user's view

Select * from User_views;

To view the user's index

Sql> select * from User_indexes;

View index number and category

Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;

View the fields indexed by the index

Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

To view the size of an index

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

where Segment_name=upper (' &index_name ');

1.1.4 viewing stored procedures

View the status of functions and procedures

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 source code for functions and procedures

Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');

1.1.5 Viewing serial numbers

SELECT * from User_sequences;

1.1.6 viewing synonyms

SELECT * from user_synonyms;

1.1.7 viewing constraints on a table

Sql> Select Constraint_name, Constraint_type,search_condition, R_constraint_name

2 from User_constraints

3 WHERE table_name = ' FAMILY ';

Export of 1.2 data

Create a Dump path

Sql> Connect/as SYSDBA

Connected.

Sql> Create directory Dump_dir as '/oracle ';

Sql> Grant Read,write on the directory Dump_dir to U1;

According to the user export, you can export stored procedures, data, views, indexes and so on.  

EXPDP system/oracle schemas=u1 dumpfile= ' u1.dump ' Directory=dump_dir version=10.2.0.1.0

2 target database operations 2.1 Create tablespaces and users

sql> Create tablespace U1

2 Logging

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

4 size 30m

5 Autoextend on

6 Next 32m

7 MaxSize 100m

8 extent management Local;

Sql> create user U1 identified by tiger default Tablespace U1;

Grant Resource,connect to U1;

2.2 follow user import

Create a Dump path

Sql> Connect/as SYSDBA

Connected.

Sql> Create directory Dump_dir as '/oracle ';

Sql> Grant Read,write on the directory Dump_dir to U1;

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

3. Other parameters 3.1 export by version

[Email protected] ~]$ EXPDP system/oracle tablespaces=u1 dumpfile= ' u1_version.dump ' Directory=dump_dir version= 10.2.0.1.0

[Email protected] oracle]$ IMPDP system/oracle tablespaces=u1 directory=dump dumpfile= ' u1_version.dump ' version= 10.2.0.1.0

3.2 Export definition

EXPDP system/oracle tablespaces=u1 dumpfile= ' u1_all.dump ' directory=dump_dir version=10.2.0.1.0 CONTENT=ALL

3.3 Export only stored procedures

procedure index table function view database LINK synonym package BODY sequence lob package procedure

[Email protected] oracle]$ EXPDP system/oracle schemas=u1 dumpfile= ' u1_p.dump ' Directory=dump_dir version=10.2.0.1.0 Include=procedure

Database outage Migration oracle10g instances