Database-table space, database backup (Implementation)

For the first time in recent years, the implementation document has been written, and some gains have been recorded in time for future use.

database table Space

The Oracle database is partitioned into logical areas called the table spaces--forming the logical structure of the Oracle database.

an Oracle database can have one or more table spaces ,

A table space corresponds to one or more physical database files.

The tablespace is the smallest unit of Oracle database recovery, accommodating many database entities such as tables, views, indexes, clusters, fallback segments, and temporary segments.

Each Oracle database has a system table space, which is created automatically when the database is created.

The system tablespace must always be online because it contains the basic information required to run the database (data dictionary for the entire database, online help mechanism, all fallback segments, temporary segments and bootstrap segments, all user database entities, tables for other Oracle Software PRODUCT requirements).

An Oracle database for a small application typically includes only the system table space, whereas a slightly larger application with an Oracle database with multiple table spaces can be more convenient for database use.

For example: Easy to understand, the Oracle database as a real room, the table space can be regarded as the space of the room, is free to allocate, in this space can be stacked multiple boxes (chests can be regarded as database files), the box is loaded with objects (objects as a table). User-specified tablespace that is, you want to put the table that belongs to this user in that room (table space).

Tablespace is a virtual concept that can be infinitely large, but requires data files as a carrier.

1) Querying the current user table space

Select Default_tablespace from dba_users where username= ' Lhmz_oa ';

Where Lhmz_oa is the user name of the user I am currently logged into.

2) querying all table spaces

SELECT * from Dba_tablespaces;

The other method can also be queried

SELECT * from V$tablespace;

3) Query the user under table space

Select distinct S.owner from dba_segments s where s.tablespace_name = ' Lhmz_oa ';

Where Lhmz_oa is the table space name

Database backup

1) View the Management Manager directory

SELECT * from Dba_directories;

2) Create a logical directory (created with system)

Create directory Dir_work as ' C:\Test ';

3) Export by user

Example:

EXPDP Lhmz_oa/[email protected] Schemas=lhmz_oa directory=dir_work dumpfile=lhmz_oa.dmp logfile=lhmz_oa.log

4) Import by user

Example:

IMPDP System/[email protected] directory=dir_zdl remap_schema=projectmanage:relief remap_tablespace=tbs_project: RELIEF Dumpfile=relief. DMP

NOTE: Exp and IMP are client tool programs that can be used either on the client or on the server side.

EXPDP and IMPDP are server-side utilities, so they can only be used on Oracle's servers and cannot be used on the client side.

These two pairs of symmetry are used, that is, who exports the corresponding import.

Database-table space, database backup (Implementation)