Procedure for Oracle DBA

The following articles mainly describe the Common commands of Oracle DBA through the code of the actual application. If you are a beginner in Oracle DBA, you can use the following articles to understand the specific operations or related functions. The following is an introduction to the text.

Create temporary tablespace

 
 

  
  
create temporary tablespace zfmi_temp  
  
  
tempfile 'D:\oracle\oradata\zfmi\zfmi_temp.dbf'  
  
  
size 32m  
  
  
autoextend on  
  
  
next 32m maxsize 2048m  
  
  
extent management local;   
 
 

The tempfile parameter must have

Create a data table space

 
 

  
  
create tablespace zfmi  
  
  
logging  
  
  
datafile 'D:\oracle\oradata\zfmi\zfmi.dbf'  
  
  
size 100m  
  
  
autoextend on  
  
  
next 32m maxsize 2048m  
  
  
extent management local;  
 
 

In the summary of common Oracle DBA commands, the datafile parameter must have

Delete users and all objects of users

 
 

  
  
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 a 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 there is content in the tablespace before the tablespace is deleted, but this parameter is not added, the tablespace cannot be deleted. Therefore, this parameter is habitually added.

Including datafiles delete data files in a 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 this method that 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 a tablespace

 
 

  
  
create user zfmi identified by zfmi  
  
  
default tablespace zfmi temporary tablespace
 zfmi_temp;  
 
 

The dentified by parameter must have

Summary of Common commands of Oracle DBA that grant all permissions to the role of message user DBA

 
 

  
  
GRANT DBA TO zfmi; 
 
 

Grant permissions to users

 
 

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

Import and Export commands:

Importing and exporting Oracle 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.

Execution environment: it can be executed in SQLPLUS. EXE or doscommand line,

In DOS, the installation directory ora81BIN in oracle 8i is set as a global path,

The EXP. EXE and IMP. EXE files in this directory are used for import and export.

Oracle is written in java. SQLPLUS. EXE, EXP. EXE, and IMP. EXE files may be packaged class files.

SQLPLUS. EXE calls the classes encapsulated by EXP. EXE and IMP. EXE to complete the Import and Export function.

The above content is an introduction to the Summary of Common commands of Oracle DBAs. I hope you will find some gains.