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;
