Oracle Create Delete Import Export command line summary

Description

The password you enter when you create the database is the default password to modify the system, and the password to enter when you log on as system and Sysman default is the modified password (the password you entered when you created the database).

If you want to create a new user, you must be logged in as System or Sysman (the two most privileged) to create

Creating User format: Create user username identified by password (for example: Create user cht identified by CHT;)

After the creation is complete, you must assign permissions, or you will not be connected to the database and Sqlplus.

So to do: Grant CONNECT,RESOURCE,DBA to CHT so that the database can be connected through Cht/cht. Those Select,update,delete,insert permissions do not have to be assigned because each user has these basic permissions by default.

Ways to connect to a database by command line:

Start = = "Run = =" cmd

Mode one:

Input Sqlplus, carriage return

Enter user name: System, carriage return

Enter Password: ORCL, carriage return

Mode two:

Enter Sqlplus SYSTEM/ORCL@ORCL, carriage return (System is username, ORCL is password, @ ORCL is the name of the library)

(After the database installation is complete, there are two system-level users:

1) system default password is: Manager

2 sys default password is: Change_on_install)

Before you create a user, you must first build the temporary table space and the data table space two table space, otherwise the system default table space is not good.

Create a temporary table space

Create temporary tablespace zfmi_temp tempfile ' d:oracleoradatazfmizfmi_temp.dbf ' size 100m autoextend on next 32m maxsize 2048m extent management Local;

Description

1, the name of the Zfmi_temp table space

2, D:ORACLEORADATAZFMI Storage database files where, is generally installed after the database has control files, data files and log files folder, plus to create the table space name +dbf (data file)

3, 100M table space Initial Size

4, 32M table space automatically increase the size of

5, 2048M table space the largest size

Create a datasheet space

Create tablespace zfmi logging datafile ' d:oracleoradatazfmizfmi.dbf ' size 100m autoextend on next 32m maxsize 2048m Exten T management local;

Create a user and specify a table space

Create user Zfmi identified by ZFMI default tablespace zfmi temporary tablespace;

Granting permissions to Users

Grant Connect,resource DBA to ZFMI; (dba: Specify all permissions)

A new user's job will be done by this step.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////

Delete users and all objects of the user

Drop user ZFMI cascade;

The cascade parameter is a cascading deletion of all objects of the user, often encountering problems that users cannot delete if they have an object without this parameter, so it is customary to add this parameter

Delete a table space

Prerequisite: Before deleting a tablespace, make sure that the tablespace is not used by another user before deleting it

Drop tablespace ZFMI including contents and datafiles cascade;

Including contents delete the contents of the tablespace, if there is content in the tablespace before the table space is deleted, but not with this parameter, the table space is not deleted, so it is customary to add this parameter

including datafiles delete data files in a tablespace

Cascade constraints Delete foreign key references for tables in tablespace

If you delete tablespace files before you delete them, you can resolve:

If you delete the data file for the table space before you clear the table space, the database will not start and shut down properly.

You can recover by using the following method (this method has been validated in oracle9i):

In the following procedure, filename is a data file that has been deleted, and if there are more than one, it needs to be executed multiple times; Tablespace_name is the name of the corresponding table space.

$ sqlplus/nolog

Sql> Conn/as sysdba;

If the database is already started, you need to first execute the following line:

sql> Shutdown Abort

Sql> Startup Mount

sql> ALTER DATABASE datafile ' filename ' offline drop;

sql> ALTER DATABASE open;

Sql> drop tablespace tablespace_name including contents;

Import Export command:

Oracle Data Import Export IMP/EXP is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and IMP commands the DMP file to be imported locally into a distant database server. With this feature, you can build two identical databases, one for testing and one for formal use.

The following is an example of an import export. (Note: Do not connect to Sql/plus, directly in DOS can be exported.) )

Data export:

1 completely export database test, user Name System Password Manager exported to D:daochu.dmp

Exp System/manager@test file=d:daochu.dmp full=y

2 Exporting a table from the system user in the database to the SYS user

Exp system/manager@test file=d:daochu.dmp owner= (System,sys)

3 Export the table inner_notify, Notify_staff_relat in the database

Exp AICHANNEL/AICHANNEL@TESTDB2 file= d:datanewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the fields in the table table1 in the database filed1 with the data beginning with "00"

Exp system/manager@test file=d:daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

The above is commonly used for export, for compression, both with WinZip to DMP file can be very good compression.

You can also implement it by adding compress=y to the command below.

Import of data

1 Import the data from D:DAOCHU.DMP into the test database.

Imp system/manager@test file=d:daochu.dmp

Imp aichannel/aichannel@hust full=y file=d:datanewsmgnt.dmp ignore=y

There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.

Just add ignore=y to the back.

2 Import the table table1 in D:daochu.dmp

Imp system/manager@test file=d:daochu.dmp tables= (table1)

Basically, the import export above is sufficient. In many cases, you should first delete the table completely and then import it.

Attention:

The operator must have sufficient permissions, and the permission is not sufficient for it to prompt.

Database can be connected to. You can use tnsping test to get the database test to connect.

Appendix I:

Actions to increase user access to import data

First, start sql*puls

Second, to System/manager landing

Third, create user username identified by password (if you have already created a user, this step can be omitted)

Four, GRANT create User,drop user,alter USER, create any VIEW,

DROP any view,exp_full_database,imp_full_database,

Dba,connect,resource,create Session to User name

V, run-cmd-into the directory where the DMP file resides,

Imp Userid=system/manager full=y file=*.dmp

or imp Userid=system/manager full=y file=filename.dmp

To execute the example:

F:workoracle_databackup>imp userid=test/test full=y file=inner_notify.dmp

Screen display

Import:release 8.1.7.0.0-production on Thursday February 16 16:50:05 2006

(c) Copyright to Oracle Corporation. All rights reserved.

Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production

To export a file created by export:v08.01.07 through a regular path

Import in the ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed

The export server uses the UTF8 NCHAR character set (possible ncharset conversions)

. Importing Aichannel objects to Aichannel

. . Importing table "Inner_notify" 4 rows are imported

Ready to enable constraints ...

The import was successfully terminated, but a warning appears.

Appendix II:

Oracle does not allow direct changes to the owner of the table, using Export/import to achieve this.

First set up Import9.par,

Then, when used, the commands are as follows: Imp Parfile=/filepath/import9.par

Examples of Import9.par are as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: The user who changes the table owner from Fromuser to Touser,fromuser and Touser can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/backup/ctgpc_20030623.dmp

Log==/backup/import_20030623.log