Summary of commands such as Oracle creation/deletion, import/export

This document describes how to create and delete users and roles in Oracle. importing and exporting data imp/exp is equivalent to restoring and backing up oracle data. The exp command can export data from the remote database server to the local dmp file, and the imp command can import the dmp file from the local to the distant database server. This function can be used to build two identical databases, one for testing and the other for formal use.

// Create a temporary tablespace

Create temporary tablespace zfmi_temp
Tempfile 'd: \ oracle \ oradata \ zfmi \ zfmi_temp.dbf'
Size 32 m
Autoextend on
Next 32 m maxsize 2048 m
Extent management local;

// The tempfile parameter must have

// Create a data table space

Create tablespace zfmi
Logging
Datafile 'd: \ oracle \ oradata \ zfmi. dbf'
Size 100 m
Autoextend on
Next 32 m maxsize 2048 m
Extent management local;

// The datafile parameter must have

// Delete the user and all objects of the user

Drop user zfmi cascade;

// The cascade parameter is used to cascade the deletion of all objects of the user. if the user has an object but does not add this parameter, the user cannot delete it. Therefore, this parameter is habitually added.

// Delete the tablespace
Prerequisites: before deleting a tablespace, make sure that the tablespace is not used by other users before deleting it.

Drop tablespace zfmi including contents and datafiles cascade onstraints;

// Including contents deletes the content in the tablespace. If the content in the tablespace is not added before the tablespace is deleted, the tablespace cannot be deleted. Therefore, this parameter is habitually added.
// Including datafiles Delete the data files in the tablespace
// Cascade constraints

If the tablespace file is deleted before the tablespace is deleted, the solution is as follows:

If the data file corresponding to the tablespace is deleted before the tablespace is cleared, the database cannot be started or shut down normally.
You can use the following method to restore data (this method has been verified in oracle9i ):
In the following process, filename is a deleted data file. If there are multiple data files, it needs to be executed multiple times. tablespace_name is the name of the corresponding tablespace.
$ Sqlplus/nolog
SQL> conn/as sysdba;
If the database has been started, run the following line first:
SQL> shutdown abort
SQL> startup mount
SQL> alter database datafile 'filename' offline drop;
SQL> alter database open;
SQL> drop tablespace tablespace_name including contents;

// Create a user and specify the tablespace

Create user zfmi identified by zfmi
Default tablespace zfmi temporary tablespace zfmi_temp;

// The identified by parameter must have

// Grant all permissions for the message user DBA role

Grant dba to zfmi;

// Grant permissions to users

Grant connect, resource to zfmi; (db2: Specify all permissions)

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page