Oracle database Schema quick migration from the test environment to the production environment, oracleschema

Oracle database Schema quick migration from the test environment to the production environment, oracleschema

Background:

To launch the production environment, you need to migrate the five schemas Schema1, Schema2, Schema3, Schema4, and Schema5 of the test environment database to the production environment, you have no operation permissions on the database server in the test environment and production environment, but you have the TNS information of the database. You can connect to the database locally using pl/SQL, the ultimate goal is to migrate the tables, views, packages, sequences, synonyms, and other database objects in the five schemas of Schema1 and Schema2 in the test environment to the production environment together, delete the data in the corresponding business table to ensure the normal operation of the production environment interface. The detailed migration steps are as follows:

Migration steps

1. If no tablespace is created, execute the following statement to create a tablespace. In the target database (log on to the target database using sys), create multiple tablespaces as to the Schema, table space size depends on the situation

Create tablespace Schema1_DATA

Datafile '+ DATA_PORTAL/PORTALPROD/schema1_data.dbf' -- path/file_name

Size 50 m

Autoextend on next 500 m maxsize 20480 m;

2. Create and authorize Scheam. Delete it before creating a new Scheam (sys logs on to the target database)

Drop user Schema1 cascade;

Create user Schema1 identified by "Schema1pwd"

Default tablespace Schema1_DATA

Temporary tablespace TEMP

Profile DEFAULT;

Grant create sequence to Schema1;

Grant create session to Schema1;

Grant create table to Schema1;

Grant create type to Schema1;

Grant create view to Schema1;

Grant debug any procedure to Schema1;

Grant debug connect session to Schema1;

Grant drop any table to Schema1;

Grant insert any table to Schema1;

Grant select any sequence to Schema1;

Grant unlimited tablespace to Schema1;

Grant update any table to Schema1;

3. Run the cmd command locally to open the dos window and run the command to export the dmp file corresponding to the Schema. If sqlplus is not an internal command error, check the environment variable, add the following configuration to Path: C: \ OralceXE \ app \ oracle \ product \ 11.2.0 \ server \ bin

4. Execute the Export command in the dos window to export the source database Schema to the local device. The other four schemas are exported in the same way.

Expschema1/test environment password @ xx. xx/test environment sid rows = y file = C: \ DB \ schema1.dmp log = C: \ DB \ schema1_exp.log;

5. Execute the following import command in the dos window to import the dmp file exported in Step 4 to the production environment.

Imp schema1/production environment password @ file = xx. xx. xx. xx/production environment sidC: \ DB \ schema1.dmp fromuser = schema1 touser = schema1 ignore = Y log = C: \ DB \ schema1_imp.log

6. Execute the delete script to delete business table data.

7. Check whether the package, view, table, and synonym of each Schema can be compiled normally.