Oracle import export

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

An import / export command parameters.(imp/exp)


1.userid the specified database user performs import and export operations, the parameter name can be omitted. An example: userid=scott/tiger


2.file specify the export documents produced, to read the file import. An example: (file= d:/myoracle/dept.exp)


3.tables specifies the table with import and export: tables=(dept,emp)


4.owner the specified user are derived: owner=(system,sys)


5.full specifies whether to import and export of the entire database. An example: full=y


6.grants specifies whether the derived table permissions. An example: grants=y


7.rows specifies whether or not the only import export table structure and table on the object, but does not import and export data in a table. An example: rows=y


8.log import and export log to write to the file name


9.indexes do you want to import and export table index cases: indexes=y


10.compress
Export should be compressed into a range of pieces of the segment, which will affect the storage clause. The import table (more than 80m), memory allocation failure,

The default exp, compress = y, also is to compress all the data in a data block. Imported, if it does not exist for a large data block,

It will import failed. Export large table above 80m, remember compress= n, it will not cause this error.

Command its own parameters for the following imp


1.show documents are displayed, rather than executed, the SQL statement can contain view files in: show=y


In the implementation of 2.ignore marked 'create' command, whether to ignore the error encountered. If the object is imported already exists,
Use this parameter cases: ignore=y


3.fromuser
In the case of full=n, only parameters. It is a series of database account, read the object from the export dump file.
An example: fromuser=system


A series of 4.touser database account, derived in the dump file to import these account cases: touser=system


5 note: imp can successfully into the low version of the exp files generated, unable to import high version of the exp file generated

Two import and export of the user table.

1.Derived table (multiple tables in parentheses separated by commas)
exp clq/clq file=d:/myoracle/dept.exp tables=dept

2 import table (imported from their export)
imp clq/clq file=d:/myoracle/dept.exp tables=dept

3 import table (import from other user derived)
imp system/manage file=d:/myoracle/dept.exp tables=dept fromuser=clq

Three. Import and export of user mode (stored procedure contains all the objects, table, index,)

1 export user
exp system/manager@orcl file=d:/myoracle/clq.exp owner=clq grants=y rows=y

2 import users
imp system/manager@orcl file=d:/myoracle/clq.exp fromuser=clq touser=clq

Import and export of four, database

1 database exports will among other database objects, object sys users have all export
exp system/manage@orcl full=y file=d:/myoracle/database.exp grants=y rows=y

Into the 2 database (mainly used in different versions, transfer data between different databases under the operating system)
imp system/manage@orcl full=y file=d:/myoracle/database.exp ignore=y

Five import and export table space.

The 1 table space is derived
(1)Will the tablespace is read-only
alter tablespace myspace read only;
(2)Using exp derived table space structure information to the metadata file, do not export the table data,
The transport_tablespace parameter settings are derived metadata table space
exp 'system/manage as sysdba' file=d:/myoracle/tablespace.exp
transport_tablespace=y tablespaces=myspace
(3)The table space is writable
alter tablespace myspace read write;
(4)The system tablespace data files and metadata file is copied to the target database in the.

Into the 2 table space, import requirements database has the same character set, in the target database cannot have the same name as the table space

(1)Use the imp command will be imported into the target database table space (datafiles specifies the data file table space)
imp 'system/manage as sysdba' file=d:/myoracle/tablespace.exp
transport_tablespace=y datafiles=(/home/oracle/mytablespace.dbf)

Six. Create and delete the table space.

Create 1 table space
create tablespace myspace
datafile 'c:\app\clq\oradata\orcl\myspace.dbf' size 100 m
autoextend on next 10 m maxsize unlimited
minimum extent 512 k
default storage(
initial 512 k
next 512 k
minextents 1
maxextents unlimited
pctincrease 0
)
online
permanent;
(1)Tablespace points out that table space
(2)The datafile: tablespace data file path
(3)Size: is initially set to 100m
(4)AUTOEXTEND indicates whether automatic extension. Off on said | automatic expansion is closed.
Next said the data file is full, extended size.
(5)mininum extent integer [k|m]
The smallest value range in the table space;. This parameter can reduce the space debris, guarantee in the range table space is the number of integer times of value.
(6)default storage_clause
Storage clause of the declaration by default.
(7)online|offline
Alter table space states. Online enables effective immediately create a tablespace. This is the defaultvalue.
Offline to create a tablespace is invalid, the value, can be obtained from the dba_tablespace;.
(8)permanent|temporary
Attribute table space, table space is permanent or temporary tablespace. Permanent tablespace is stored in the persistent object,
Temporary tablespace is stored in the temporary object in the session life cycle

Create temporary table space 2
Temporary tablespace for temporary storage of data, when a user performs a sort operation, create an index that, will generate a lot of intermediate results,
These temporary data storage in the first sort area in pga. When the sorting is not large enough to accommodate the area data, will use the temporary table space.
If not for the user to specify the temporary table space, then the user to perform a sort operation will take
The system table space as a temporary table space, and in the system table to create the temporary segment space, the temporary storage of data.
It has lots of storage space debris in the system table space, thereby reducing the database performance.
create temporary tablespace mytempspace
tempfile 'c:\app\clq\oradata\orcl \mytempspace.dbf'
size 200m
autoextend on
next 50m maxsize 1024m
extent management local;

Remove the 3 table space
drop tablespace mysapce including contents and datafiles

Seven create and delete users.

Create 1 users
create user clq identified by clq
default tablespace myspace
temporary tablespace mytempspace
quota unlimited on myspace;

(1)User specifies the name of the user, identified by specifies the password.
(2)default tablespace, For the user to specify the default tablespace
(3)Temporary tablespace for the user to specify a default temporary tablespace
(4)Quota is a user specified quota in the table space, unlimited is no limit.

2 for the user to give permissions, the user just created without any permission, need to users with permission
grant connect to clq;
grant exp_full_database to clq;
grant imp_full_database to clq;
grant resource to clq;

 3 revoking permissions, basic grammar with grant, keywords for revoke

 4 see permissions

  select * from user_sys_privs;//View the current user all permissions

  select * from user_tab_privs;//Check your user table permissions

5 delete users, if some database object contains a user mode, so the user cannot be deleted,

If forced to delete the user, can add the cascade option on the command.
drop user clq;
drop user clq cascade;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Jerome at December 10, 2013 - 11:48 PM