Oracle Export Import Database

• I. Allocating space to empty tables:

This step must be done, otherwise an empty table cannot be exported.

First connect the library you want to export, and execute the following SQL on the library:

Select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from user_tables where num_rows = 0

The execution result is a series of SQL statements, similar to ALTER TABLE TableName allocate extent; Select all the results, copy them, and execute them uniformly in the library.

I'm looking at a lot of people on the Internet. Where num_rows = 0, meaning I understand that only the table with data bar number 0 generates the SQL statement to execute, but it still seems to have a missing table, I use

Select Table_name,num_rows from User_tables in the library to find the value of num_rows is null, not 0, anyway, you need to choose to add it

Of course, there is no where condition, then all the tables are set up again, in fact there is a table of data do not need to set, if not, add a where num_rows = 0 or num_rows is null, so there should be no missing table.

• Two. Export the DMP file

Execute the command in CMD to export the dmp file:

Exp username / password @ listener name file=e:\ database name. DMP owner=log= e:\ the log name. log Indexes=statistics=None      

The owner is added here to export all tables under this user

Note After the listening name, generally your Oracle installation directory has a Tnsnames.ora file, played PL/SQL should know, often to change it, in this file to join the monitoring configuration

After a period of time the console will prompt for export success, depending on the database size.

• Three. Create a table space

• 1. See where the file path of the previous database corresponds:

SELECT T1.name, t2.name from V$tablespace T1, V$datafile T2 WHERE t1.ts#=t2.ts# ORDER by T1.name;

Note the user who executes the command locally needs DBA authority, otherwise it will be prompted that the table does not exist, can log in to Sqlplus, log on with Conn/as SYSDBA, and Grant "DBA" to the user; or execute the SQL directly in SYSDBA to see the results.

• 2. Create a table space

Create tablespace Mytisjs    ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\mytisjs.dbf ' (mytisjs.dbf in front of the path for you to query out the path, in fact, the path can be written anywhere, just and the previous database in the same location is better   ) size 2000m  on   Next 500m maxsize 10096m   

• 3. Create a temporary table space

Create Temporary tablespace mytisjs_temp    ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\mytisjs_temp.dbf '   size 500m   on   next  500m maxsize 2048m   extent Management Local ;

• 4. Create a user

CREATE USER  by Test DEFAULT tablespace "Mytisjs"   ( specify Tablespace as the tablespace you just created, notice that the tablespace name is capitalized ) Temporary Tablespace "Mytisjs_temp";

• 5. Granting Permissions

GRANT  to   grant to grant to

• Four. Importing DMP files

Import with imp command

IMP test/test@mytisjsfile=log=E:\tisjs. Log  Full =y Ignore=y

where test/test is username/password MYTISJS and the same as before, in the Tnsnames.ora file to add the corresponding configuration, if I want to import local, the host is localhost,file after the path is the DMP file you exported before the path, Log is the same.

Waiting for a while will prompt the import success, the database can be used.

If the DMP file is locally imported to the local database, the direct imp test/test file=e:\tisjs.dmp log=e:\tisjs.log full=y ignore=y can be used without configuring monitoring

• Five. Table Space Related notes:

• 1. View the table space for the database, including the temporary table space

    Select *  from (      SelectA.tablespace_name, To_char (a.bytes/1024x768/1024x768,'99,999.999') Total_bytes, To_char (b.bytes/1024x768/1024x768,'99,999.999') Free_bytes, To_char (a.bytes/1024x768/1024x768 -B.bytes/1024x768/1024x768,'99,999.999') Use_bytes, To_char (1 -B.bytes/A.bytes)* -,'99.99')|| '%'  Use       from(SelectTablespace_name,sum(bytes) bytes fromDba_data_filesGroup  byTablespace_name) A, (SelectTablespace_name,sum(bytes) bytes fromDba_free_spaceGroup  bytablespace_name) bwhereA.tablespace_name=B.tablespace_nameUnion  All      SelectC.tablespace_name, To_char (c.bytes/1024x768/1024x768,'99,999.999') Total_bytes, To_char (c.bytes-d.bytes_used)/1024x768/1024x768,'99,999.999') Free_bytes, To_char (d.bytes_used/1024x768/1024x768,'99,999.999') Use_bytes, To_char (d.bytes_used* -/C.bytes,'99.99')|| '%'  Use       from      (SelectTablespace_name,sum(bytes) bytes fromDba_temp_filesGroup  bytablespace_name) C, (SelectTablespace_name,sum(bytes_cached) bytes_used fromV$temp_extent_poolGroup  bytablespace_name) dwhereC.tablespace_name=d.tablespace_name)

• 2. View the current user's usage of the tablespace and the default tablespace, if the result is too many can add a where condition to filter to the current user

Select Username,default_tablespace,temporary_tablespace  from Dba_users

• 3. The temporary table space is full, the query may error, execute the following command to shrink the temporary tablespace

ALTER SPACE

Where mytisjs_temp is the temporary table space name to shrink

• 4. Delete User and table spaces

DROP USER CASCADE ; Drop  and datafiles; Drop  and Datafiles;

Oracle Export Import Database