Summary of common Oracle database maintenance operations

We know that database maintenance is an essential part of database security and stability. This article summarizes someOracle DatabaseSome common maintenance operations, including some common maintenance operations, some system maintenance operations andNamespaceAnd so on, let's take a look at it!

General:

In the sqlplus command line, after the database operation is completed, the command must be followed by commit to take effect.

It mainly listens to two ports: 1521 is the database connection port and 8080 is the server port.

After Oracle is installed by default, an http server is provided with the user management interface in the form of web. The server port 8080 is the same as the default port of tomcat, which is a common cause that tomcat cannot be started, I usually modify the tomcat configuration file server. xml, and change the port of http1.1 to 8081.

System Maintenance:

Login

Sqlplus username/password @ addr [as sysdba, sysoper]

If you do not enter the address, the default value is localhost. If you do not enter the identity, the default value is sysoper.

Query sid

Select instance_name from v $ instance

Query database versions

Select version FROM Product_component_version Where SUBSTR (PRODUCT, 1, 6) = 'oracle ';

Query listener: lsnrctl status -- In cmd instead of sqlplus

Start listening: lsnrctl start -- in cmd, instead of sqlplus

Stop listening: lsnrctl stop -- In cmd instead of sqlplus

Create user

 
 

  
  
create user weinianjie identified by "123" default tablespace sheep temporary tablespacetemp profile default;  
  
  
 
  
  
grant resource,connect,dba to weinianjie; 
 
 

Query current user

Show user;

Namespace:

Explanation:

Baidu encyclopedia original saying: "ORACLE database is divided into logical regions called tablespace-forming the logical structure of ORACLE Database. One ORACLE database can have one or more Oracle databases, while one tablespace corresponds to one or more physical database files. Table space is the minimum unit for ORACLE database recovery. It contains many database entities, such as tables, views, indexes, clustering, rollback segments, and temporary segments.

Each ORACLE database has a SYSTEM tablespace, which is automatically created when the database is created. The SYSTEM tablespace must always be online, because it contains the basic information required for running the database (data dictionary, online help mechanism, all rollback segments, temporary and auto-lifting segments, all user database entities, tables required by other ORACLE software products )."

In my understanding, tablespaces are similar to databases. A tablespace corresponds to one or more physical files. Each user belongs to one or more tablespaces, one of which is the default. You do not need to create a tablespace when connecting to the database. The objects created by the user are stored in the default tablespace.

A namespace defines a set of object types. In a namespace, the object name must be unique. Of course, the same name can be used in different namespaces.

The following object types share the same namespace:

 
 

  
  
? Tables  
  
  
 
  
  
? Views  
  
  
 
  
  
? Sequences  
  
  
 
  
  
? Private synonyms  
  
  
 
  
  
? Stand-alone procedures  
  
  
 
  
  
? Stand-alone stored functions  
  
  
 
  
  
? Packages  
  
  
 
  
  
? Materialized views  
  
  
 
  
  
? User-defined types 
 
 

Create a data table space: create tablespace sheep datafile 'C:/sheep. dbf' size 20 m autoextend on;

Create temporary tablespace: create temporary tablespace sheep tempfile 'C:/sheep. dbf' size 20 m autoextend on;

Query space:

 
 

  
  
select tablespace_name from dba_tablespaces;  
  
  
 
  
  
select tablespace_name from user_tablespaces; 
 
 

Query the physical conditions of a table space:

 
 

  
  
Select * from dba_data_files where tablespace_name = 'system ';
  
  
 
  
  
Select * from dba_temp_files where tablespace_name = 'temp '; -- it must be in upper case, even if you didn't use upper case when creating the space
 
 

Query tables in a table space:

Select table_name from dba_all_tables where tablespace_name = 'users ';

Table:

Query all tables in the current user space:

Select tname from tab

Sequence:

Query all sequences of the current user space:

Select squence_name from seq

Creation sequence:

Create sequence user_seq minvalue 1 maxvalue 99999 start with 1 increment by 1 nocache nocycle

Sequence:

Insert into user values (user_seq.nextval );

Script:

 
 

  
  
Begin -- must begin with begin.
  
  
 
  
  
For column _ in (select * from tb1) loop
  
  
 
  
  
Insert into tb2 values (column _. field1 );
  
  
 
  
  
End loop;
  
  
 
  
  
End;
 
 

Java interaction:

Driver name: oracle. jdbc. driver. OracleDriver

Connection string: jdbc: oracle: thin: @ localhost: 1521: xe. The last value is sid.

For more information about the common maintenance of Oracle databases, see the article http://database.51cto.com/oracle.