Shulou(Shulou.com)06/01 Report--

Oracle basic management commands

After installing the oracle database, we need to carry out a series of management of the database. The following is to introduce the opening of the Oracle database, the startup of the listener, the pluggable database and the management of table space and table space.

What is the tablespace of Oracle? Oracle database stores physical tables through tablespaces. A database instance can have N tablespaces and one tablespace can have N tables. The type of tablespace is SYSTEM system tablespace, which is used to store management information about tablespace names, control files, data files and so on. SYSAUX assists system tablespaces to reduce the load on system tablespaces. TEMP, temporary tablespace, holds 00:00 tables and temporary data for sorting. USERS user tablespace, which stores permanent user objects and private information, also known as data tablespace. Each data has a user tablespace, and the created user is placed in the user tablespace, otherwise in the system tablespace. UNDO redoes tablespaces to help roll back uncommitted transaction data.

The biggest feature of Oracle12C is its support for hot-swappable databases: one or more PDBS,PDBS can be backwards compatible in CDB, and PDBS can be manipulated as before in the database, each of which can be called a container. Therefore, ROOT (root) is a container, Seed (seed) is a container, and each PDB is a container. Each container has a unique ID and name in CDB.

The following is the actual operation of the opening of the Oracle database, the startup of the listener, the hot-swappable database and the management of the table space.

1. Open the oracle database

[root@bogon ~] # su-oracle # switch to oracle user first

[oracle@bogon ~] $sqlplus / as sysdba # Log in using the sys supreme administrator

SQL > startup

Database startup trilogy:

Start routine

Load the database

Open the database

2. Four ways to close the database

●: shutdown normal # shuts down the database after all transactions are committed

●: shutdown immediate # transaction continues, commits normally, and shuts down the database immediately (recommended)

●: shutdown transactional # rollback all transactions and shut down the database immediately

●: shutdown abort # forced database shutdown (generally not used)

3. By default, port 5500 can be used to access the monitoring page after installation.

There is a minor problem here, that is, after restarting the oracle server, you cannot log in to port 5500 to access it. The reason is that port 5500 is not enabled after restart. You need to perform the following operations:

[oracle@bogon ~] $lsnrctl start # enable listening

If listening is turned on, an error message is reported.

Listening Endpoint Summary.

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=127.0.0.1) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

Listeners do not support services

Need to use root user input

[root@bogon ~] # xhost +

Then switch to oracle user input

[oracle@bogon ~] $export DISPLAY=:0.0

4. Create a database

[oracle@bogon ~] $dbca # and then wait for it to jump out of the graphical interface.

Database path storage location

5. Tablespace management

First create the tablespace with the following command:

SQL > create tablespace works # create a tablespace named works

2 datafile'/ orc/app/oracle/oradata/works01.DBF' # defines the path, and the file extension is .DBF

3 size 10m autoextend on; # space size is 10m

Two methods of adjusting tablespace

Method 1, modify the size directly:

SQL > alter database datafile

2'/ orc/app/oracle/oradata/works01.DBF'

3 resize 200M

Method 2, add a file:

SQL > alter tablespace works

2 add datafile

3'/ orc/app/oracle/oradata/works02.DBF'

4 size 60M autoextend on

View tablespace size

The command to change tablespace permissions is as follows:

SQL > alter tablespace works read write; # change works tablespace to readable and writable

SQL > alter tablespace works read only; # change works tablespace to read-only

Delete tablespace

SQL > drop tablespace works including contents; # is deleted along with the data in the tablespace (including contents does not delete the contents of the tablespace)

6. Pluggable database (switch between container database and regular database CDB and PDB)

SQL > show con_name # View database schema

SQL > show pdbs; # View all container databases

SQL > alter pluggable database orclpdb open; # switch to plug-in database mode

SQL > alter session set container=orclpdb; # access to pluggable database

Switch back to the default database mode

SQL > shutdown immediate # shut down the pluggable database first

SQL > startup # reopen

SQL > alter session set container=cdb$root; # switch back to the default database mode

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.