36.存储结构

1.存储结构概述

 

--：表空间-->段-->区-->块-->行和列
--Oracle DB在逻辑存储上将数据存储在表空间中，在物理上将数据存储数据文件中
--表空间：
--只能属于一个数据库
--包括一个或多个数据文件
--可进一步划分为逻辑存储单元

--数据文件：
--只能属于一个表空间和一个数据库
--是存储方案对象的数据的资料档案库

--SYSTEM：系统表空间，存放数据字典
--SYSAUX：数据库操作工具
--UNDOTBS：回滚段信息
--TEMP：临时段和临时表，排序
--USERS：用户表空间
--EXAMPLE：例子表空间

--OMF
--按照数据库对象而不是文件名指定文件操作
--db_create_file_dest:定义数据文件和临时文件默认文件系统目录的位置
--db_create_online_log_dest_n:定义重做日志和控制文件的位置
--db_recovery_file_dest；快速恢复区的默认位置

--扩大数据库
---创建新表空间
---将数据文件增加到新的表空间
---增加数据文件的大小
---动态扩展数据文件

2.表空间的管理演示

--查看表空间
sys@ORCL 2023-03-04 09:07:53> r
  1* select tablespace_name,file_name from dba_data_files

TABLESPACE_NAME 	       FILE_NAME
------------------------------ ----------------------------------------
USERS			       /oradata/ORCL/users01.dbf
UNDOTBS1		       /oradata/ORCL/undotbs01.dbf
SYSTEM			       /oradata/ORCL/system01.dbf
SYSAUX			       /oradata/ORCL/sysaux01.dbf

sys@ORCL 2023-03-04 09:09:16> select tablespace_name,contents,retention,bigfile from dba_tablespaces;

TABLESPACE_NAME 	       CONTENTS 	     RETENTION	 BIG
------------------------------ --------------------- ----------- ---
SYSTEM			       PERMANENT	     NOT APPLY	 NO
SYSAUX			       PERMANENT	     NOT APPLY	 NO
UNDOTBS1		       UNDO		     NOGUARANTEE NO
TEMP			       TEMPORARY	     NOT APPLY	 NO
USERS			       PERMANENT	     NOT APPLY	 NO

--表空间的建立
create bigfile|smallfile tablespace <> datafile '....' size <> autoextend on|off;
create tablespace a1 datafile '/oradata/ORCL/a1.dbf' size 2M autoextend on max 1g;

--自动存储目录
alter session set db_create_file_dest='/oradata/ORCL/';
create tablespace a1;

--undo表空间
--一个实例一个undo表空间，两个实例两个undo表空间
create bigfile|smallfile undo tablespace <> datafile '...'
create undo tablespace undo1;
create undo tablespace undo1 datafile '+db' size 10m autoextend on;

--temporary
create bigfile|samllfile temporary tablespace tempfile '....' size 1k|1m ...;
--临时表空间区，固定大小

sys@ORCL 2023-03-04 09:09:18> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
SYSTEM			       ONLINE
SYSAUX			       ONLINE
UNDOTBS1		       ONLINE
TEMP			       ONLINE
USERS			       ONLINE

sys@ORCL 2023-03-04 09:19:47> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
SYSTEM			       ONLINE
SYSAUX			       ONLINE
UNDOTBS1		       ONLINE
TEMP			       ONLINE
USERS			       ONLINE

Elapsed: 00:00:00.05
sys@ORCL 2023-03-04 09:19:52> alter tablespace users offline;

Tablespace altered.

Elapsed: 00:00:00.05
sys@ORCL 2023-03-04 09:20:19> alter tablespace users online;

Tablespace altered.

Elapsed: 00:00:00.03

sys@ORCL 2023-03-04 09:21:34> select file#,name,status,enabled from v$datafile;

     FILE# NAME 									    STATUS  ENABLED
---------- -------------------------------------------------------------------------------- ------- ----------
	 9 /oradata/ORCL/orclpdb01/system01.dbf 					    SYSTEM  READ WRITE
	10 /oradata/ORCL/orclpdb01/sysaux01.dbf 					    ONLINE  READ WRITE
	11 /oradata/ORCL/orclpdb01/undotbs01.dbf					    ONLINE  READ WRITE
	12 /oradata/ORCL/orclpdb01/users01.dbf						    ONLINE  READ WRITE

sys@ORCL 2023-03-04 09:21:34> select file#,name,status,enabled from v$datafile;

     FILE# NAME 									    STATUS  ENABLED
---------- -------------------------------------------------------------------------------- ------- ----------
	 9 /oradata/ORCL/orclpdb01/system01.dbf 					    SYSTEM  READ WRITE
	10 /oradata/ORCL/orclpdb01/sysaux01.dbf 					    ONLINE  READ WRITE
	11 /oradata/ORCL/orclpdb01/undotbs01.dbf					    ONLINE  READ WRITE
	12 /oradata/ORCL/orclpdb01/users01.dbf						    ONLINE  READ WRITE

Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 09:22:19> alter database datafile 12 offline;

Database altered.

Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 09:22:45> alter database datafile 12 online;
alter database datafile 12 online
*
ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/ORCL/orclpdb01/users01.dbf'


Elapsed: 00:00:00.03
sys@ORCL 2023-03-04 09:22:50> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /oradata/archivelog
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence	       19
sys@ORCL 2023-03-04 09:23:27> recover database;
ORA-01126: database must be mounted in this instance and not open in any instance


sys@ORCL 2023-03-04 09:24:34> select status from v$instance;

STATUS
------------
OPEN

Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 09:24:51> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 ORCLPDB01			  READ WRITE NO
sys@ORCL 2023-03-04 09:24:54> shutdown abort;
Pluggable Database closed.
sys@ORCL 2023-03-04 09:25:10>  recover database;
Media recovery complete.
sys@ORCL 2023-03-04 09:25:19> 
sys@ORCL 2023-03-04 09:25:20> 
sys@ORCL 2023-03-04 09:25:20> alter database open;

Database altered.

Elapsed: 00:00:00.94
sys@ORCL 2023-03-04 09:25:27> 
sys@ORCL 2023-03-04 09:25:28> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 ORCLPDB01			  READ WRITE NO
sys@ORCL 2023-03-04 09:25:32> select file#,name,status,enabled from v$datafile;

     FILE# NAME 									    STATUS  ENABLED
---------- -------------------------------------------------------------------------------- ------- ----------
	 9 /oradata/ORCL/orclpdb01/system01.dbf 					    SYSTEM  READ WRITE
	10 /oradata/ORCL/orclpdb01/sysaux01.dbf 					    ONLINE  READ WRITE
	11 /oradata/ORCL/orclpdb01/undotbs01.dbf					    ONLINE  READ WRITE
	12 /oradata/ORCL/orclpdb01/users01.dbf						    OFFLINE READ WRITE

Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 09:25:43> alter database datafile 12 online;

Database altered.

Elapsed: 00:00:00.01
sys@ORCL 2023-03-04 09:25:59> select file#,name,status,enabled from v$datafile;

     FILE# NAME 									    STATUS  ENABLED
---------- -------------------------------------------------------------------------------- ------- ----------
	 9 /oradata/ORCL/orclpdb01/system01.dbf 					    SYSTEM  READ WRITE
	10 /oradata/ORCL/orclpdb01/sysaux01.dbf 					    ONLINE  READ WRITE
	11 /oradata/ORCL/orclpdb01/undotbs01.dbf					    ONLINE  READ WRITE
	12 /oradata/ORCL/orclpdb01/users01.dbf						    ONLINE  READ WRITE
    
--表空间改名字
alter tablespace xxx rename yyy;

--删除表空间
alter tablespace drop
drop tablespace <>;
drop tablespace <> including contents; --拥有用户资料
drop tablespace <> including contents and datafile;--同时删除数据文件

--如果使用OMF，删除表空间，会自动删除数据文件
--system，sysaux，users，undo，temp不能被删除
--修改默认表空间之后，就可以删除旧表空间了
sys@ORCL 2023-03-04 09:38:04> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

PROPERTY_NAME		       PROPERTY_VALUE			   DESCRIPTION
------------------------------ ----------------------------------- -------------------------------------------------------
DEFAULT_TBS_TYPE	       SMALLFILE			   Default tablespace type
DEFAULT_EDITION 	       ORA$BASE 			   Name of the database default edition
DEFAULT_PERMANENT_TABLESPACE   USERS				   Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE        TEMP				   Name of default temporary tablespace

Elapsed: 00:00:00.00

--增加表空间大小
alter database datafile '/oradata/ORCL/orclpdb01/users01.dbf' resize 10M;

--开启自动扩展
alter database datafile '/oradata/ORCL/orclpdb01/users01.dbf' autoextend on;

--大表表空间可以开启自动增长
alter tablespace tsb1 autoextend on;

 

posted @   竹蜻蜓vYv  阅读(60)  评论(0)  编辑  收藏  举报
相关博文：
· 39.管理还原数据
· 32.数据库的创建和维护
· 【Oracle】表空间概述
· [Oracle19C 数据库管理] 管理存储与表空间
· oracle体系架构基础、逻辑存储结构
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛