Summary of basic table space management operations in Oracle databases, tables in oracle

Summary of basic table space management operations in Oracle databases, tables in oracle

DB storage hierarchy

(I drew a sketch and I will take a look at it... XD)

Manage tablespaces

-System: required to store data dictionary information. It is the first database to be created.

-New and required 10 Gb sysaux, which helps to share system load, system management, oem, and other third-party tools

-Undo stores rollback segment information and provides the transaction rollback function.

-Temp stores temporary data sorted by users.

-Index: stores the index information of a user table.

-Other data in different user tables

Obtain table space and Data File Information

TABLESPACE information: DBA_TABLESPACES V $ TABLESPACE
Data File Information: DBA_DATA_FILES V $ DATAFILE
Temporary data file information: DBA_TEMP_FILE V $ TEMPFILE

Create a tablespace

    create [smallfile|bigfile] tablespace <identName> datafile '<path&name>' [extent management local uniform] size <n>k|m|g|t

Usually not required
Are you sure you want to use bigfile or smallfile? For TB-level or higher, bigfile is generally used.
Smallfile | bigfile does not use the default value,

select property_name,property_value from database_properties where property_name like '%TBS%';

You can change the default value. alter database set default bigfile tablespace .'
You must have dba_role, sysdba, or sysoper permissions.
Extent management local uniform local management of tablespaces (LMT). bitmap is used to describe spatial distribution to reduce competition in data dictionaries. You do not need to set storage parameters for each segment. In the same version, convert the dictionary management tablespace (DMT) to LMT:

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

In different versions, DMT is converted to LMT. In the source machine, exp is used to export the tablespace, create a table structure on the target machine, and execute imp plus ignore = y to ignore the table structure and import data.

A single tablespace can contain 1024 datafiles

Tablespace status

    select tablespace_name,file#,v.status,v.enabled from dba_files d,v$datafile v where d.file_id=v.file#;    alter tablespace <tablespace_name> read only|read write|offline|online;

Tablespace online read only
System must be online and must be read write
Sysaux can be offline and must be read write
Undo must be online and must be read write

Objects in the read-only tablespace can be deleted.

Alter tablespace <old> rename to <new>; rename the tablespace. The default user tablespace/default temporary tablespace/undo_tablespace defined in undo cannot be renamed in system/sysaux/database_properties.

Tablespace size

    alter database set default smallfile|bigfile tablespace;

A large table data file can contain 4G OS blocks and 4 m OS blocks for small table data files

Automatic expansion:

alter tablespace datafile '<path & name>'|file# autoextend on|off [next <size>|maxsize <size‪>];         select tablespace_name, file_name, autoextensible from dba_data_files;

Manual expansion:

alter tablespace datafile '<path & name>'|file# resize <size>;         alter tablespace datas add datafile '<path & name>' size <size>;

(Add data files in the tablespace)

Rename a tablespace file (archive Mode)

Offline ---> operating system-level mobile data file or renaming ---> Execute

alter tablespace rename file '<old>' to '<new>';

---> Online

Mount stage ---> operating system-level mobile data file or renamed ---> execution

alter tablespace rename file '<old>' to '<new>';--->startup

Delete a tablespace

Cannot be deleted: System tablespace/undo tablespace in use/default temporary and permanent tablespace

    drop tablespace <name> [including contents [and datafiles]];

System tablespace

Data Dictionary and definition Information

Management:
Space Management
Generally, a single data file is stored in the system, and automatic scaling or bigfile is set.

Backup
Must be backed up in the open state in archive mode, hot standby (alter tablespace system begin backup; ho cp? /System01.dbf? /Bak/; alter tablespace system end backup;), or use rman (rman target/; backup tablespace system;) to back up

Restore
Logs are complete when the archive is backed up. cp? /Bak/system01.dbf? /; Recover database; or use rman: restore tablespace system;
Logs are incomplete when the archive is backed up, and no data is lost.

Cannot be offline, cannot be read-only, cannot be renamed

System auxiliary (sysaux) tablespace

10 Gb new, auxiliary system, stores third-party tools

It can be offline and cannot be read-only and cannot be deleted (startup migrate | drop tablespace sysaux at downgrade sometimes). It cannot be renamed

View

        select occupant_name,schema_name from v$sysaux_occupants;

Backup
Open backup and hot backup in archive Mode

(alter tablespace sysaux begin backup;ho cp ?/sysaux01.dbf ?/bak/;alter tablespace sysaux end backup;)

, Or use

rman(rman target /;backup tablespace sysaux;)

Backup

Restore
Logs are complete when the archive is backed up. cp? /Bak/sysaux01.dbf? /; Recover database; or use rman: restore tablespace sysaux;
No backup. In the offline status, migrate the data, rebuild the database, and migrate the data back.

UNDO tablespace

Function
Transaction rollback
Savepoint <n>; rollback to <n>;
Transaction submission
DML | DDL transaction ends: the transaction is committed by commit DML, And the DDL is automatically committed.
Flash back data

Status query
V $ rollstat (all rollback segments) v $ rollname (currently used rollback segments) dba_Segments dba_rollback_Segs
V $ transaction

View the currently used undo tablespace

    show parameter undo_tablespace;

Create

    create smallfile|bigfile undo tablespace <name> datafile <path&name> size <size>;

Delete

    drop tablespace undo including contents and datafiles;

(The currently used tablespace cannot be deleted)

Change

After renaming the default undo tablespace, Oracle automatically changes the parameter to the new name after the database is restarted.

Estimate the undo tablespace size:

undo space=(undo_retention*undo_blocks_per_second * db_block_size) + db_block_size    show parameter undo_retention;--->undo_retention    show parameter db_block_size;--->db_block_size    select sum(undoblks)/sum((end_time - begin_time)*10800) from v$undostat;--->undo_blocks_per_second 

Query the size of the current undo tablespace:

max(block_id) * db_block_size    show parameter db_block_size;--->db_block_size

(Unit: k)

    select max(block_id) from dba_extents where file_id=2;--->block_id

Backup (archive Mode)
Backup: rman or Hot Backup
No backup: whether there are other undo tasks in the system. If yes, take the corrupted ones offline, replace them with other ones, start them, delete the corrupted tablespace, and create a new one. If no, set the parameter undo_management = manual and use the hidden parameter to bring undo offline (alter system set "_ offline_rollback_segments" = true scope = spfile), and then start, see the status file in dba_rollback_Segs to check whether the segment of the corrupted tablespace has a recover. If not, delete the damaged tablespace and create a new one. If yes, use _ uploupted_rollback_segments to mark the segment, delete the corrupted tablespace and create a new one. Finally, restore the above parameters.

Articles you may be interested in:

• Detailed description of oracle tablespace table partitions and how to query oracle table partitions
• Oracle queries the table space size and usage SQL statements
• SQL statements for creating a user, role, authorization, and tablespace in Oracle