Database downtime migration oracle10g instance and oracle10g migration

Database downtime migration oracle10g instance and oracle10g migration
1. Operation 1.1 of the original database 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. Operation 2.1 of the target database creates tablespaces and users

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 are exported 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

Oracle 10 GB Data Migration

Need toad?
Log in with a dba account
Insert into A. tb_a
Select id, name, pwd from B. tb_a;
Commit;
No.

How to migrate an ORACLE database

You can directly use the ORACLE command line tool to export data by using export, and then use the import command to import data on the target server. These two statements are particularly effective for cross-platform migration and large data volumes, data rows in excel cannot exceed 65535, and the efficiency is relatively low.