oracle19c 表空间和数据文件管理

目录表空间管理临时表空间undo表空間表空间管理oracle 支持不同块尺寸的表空间只有oracle數據庫支持表空間分爲標準快的表空間和非標準快的表空間。SQL select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;-------查詢表空間的信息TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING EXTENT_MAN SEGMEN------------------------------ ---------- --------- --------- ---------- ------SYSTEM 8192 ONLINE LOGGING LOCAL MANUALSYSAUX 8192 ONLINE LOGGING LOCAL AUTOUNDOTBS1 8192 ONLINE LOGGING LOCAL MANUALTEMP 8192 ONLINE NOLOGGING LOCAL MANUALUSERS 8192 ONLINE LOGGING LOCAL AUTOEXAMPLE 8192 ONLINE NOLOGGING LOCAL AUTO我们在创建非标准块的表空间时数据库是不允许我们创建的。因为数据库的标准块默认为8k数据缓冲区会是很多个8k的内存槽如果我们设置块为16k那么这个16k的块是不能装进8k的内存槽的这个时候我们就需要去创建个16k的缓冲区以后这个缓冲区就装16k的数据块。SQL show parameter db_cache_size;----查看内存块的大小 SQL alter system set db_16k_cache_size10m;-----设置16k的内存块修改内存块的大小要在cdb$root下面更改 SQL select property_name,property_value,description from database_properties;-----查看默认的表空间 字符集默认的表空间如果在创建表的时候指定了表空间就会将数据放进指定的表空间如果没有指定那么数据就会存进默认的表空间。select tablespace_name,file_name from dba_temp_files;---------查看临时表空间的数据设置表空间为offline的3种方式normal:如果表空間的任何數據文件都不存在錯誤條件則表空間可以正常脫機。oracle數據庫在使表空間的所有數據文件脫機時會爲他們設置一個檢查點從而保證所有數據都寫入磁盤。tempory即使表空間中的一個或多個文件出現錯誤也可以暫時使表空間脫機。oracle數據庫將數據文件脫機時并在脱机时对其执行检查点。如果没有文件处于脱机状态但使用了临时子句则无需进行介质恢复。但是如果表空间的一个或多个文件由于写入错误而脱机并且已经暂时使表空间脱机则需要恢复表空间然后才能使其重新联机。immediate表空间可以立即脱机而oracle数据库不会对任何数据文件设置检查点需要进行介质恢复。SQL alter database move datafile /u01/app/oracle/oradata/CDB/user01.dbf to /u01/app/oracle/oradata/CDB/pdb1/users01.dbf;---在线移动数据文件的位置并且可以直接移动os文件的位置很神奇对吧Database altered.创建表空间SQL create tablespace ts001 datafile DATA/orcl/datafile/ts001.DBF size 100M, DATA/orcl/datafile/ts002.DBF size 100M autoextend on next 100M---当表空间的数据文件不足时会自动给数据文件增加100M,仅对第二个数据文件生效 第一个数据文件没有设置自动扩展 maxsize 2G---数据文件最大为2G EXTENT MANAGEMENT LOCAL------表空间的区为本地管理 UNIFORM SIZE 1M;-------表空间每个extent的大小为1M重置表空间的大小SQL alter database orcl datafile DATA/orcl/datafile/ts001_2.dbf resize 200M;------第一种方式修改数据文件的大小 SQL alter tablespace ts001 add datafile DATA/orcl/datafile/ts001_2.dbf size 100M autoextend on next 50m;-----第二种方式为表空间增加数据文件重命名数据文件第一种方式停库操作关闭数据库移动操作系统文件然后将数据库启动到mount状态修改控制文件信息最后需要删除原来的文件SQL alter database rename file DATA/orcl/datafile/ts001_1.dbf to DATA/orcl/datafile/ts1_001.dbf; Database altered.第二种方式不需要停库将表空间离线移动操作系统文件然后修改修改控制文件信息再将表空间上线最后需要删除原来的操作系统文件SQL alter tablespace ts001 offline; Tablespace altered. SQL alter tablespace rename file DATA/orcl/datafile/ts001.dbf to DATA/orcl/datafile/ts001_1.dbf; Tablespace altered. SQL alter tablespace ts001 online;删除表空间DROP TABLESPACE DUMP INCLUDING CONTENTS AND DATAFILES;--------删除表空间的同时删除表空间的内容和操作系统数据文件临时表空间存储排序的数据临时表空间可以被多个用户共享其中不能包含任何永久对象。默认临时表空间在同一时间只能使用一个如果在创建数据库时没有指定默认临时表空间在创建用户时也没有指定默认的临时表空间那么需要使用到临时表空间时会将临时数据存进system表空间我们一般会在创建数据库时指定默认的临时表空间而不是在创建用户的时候指定默认临时表空间。SQL select tablespace_name,status from dba_tablespaces;-------查看所有表空间的状态 TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE DUMP ONLINE 6 rows selected. SQL select name from v$datafile;------这样查询时看不到临时表空间的因为没有数据文件 /u01/app/oracle/oradata/CDB/system01.dbf /u01/app/oracle/oradata/CDB/sysaux01.dbf /u01/app/oracle/oradata/CDB/undotbs01.dbf /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/CDB/users01.dbf /u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf /u01/app/oracle/oradata/CDB/pdb1/system01.dbf /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf /u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf /u01/app/oracle/oradata/CDB/pdb1/users01.dbf /u01/app/oracle/oradata/CDB/pdb2/system01.dbf /u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf /u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf 14 rows selected. SQL alter tablespace tmpe add tempfile/u01/app/oracle/oradata/CDB/temp03.dbf size 50m; Tablespace altered. SQL select tablespace_name,file_name from dba_temp_files;-------现在查询就有数据了 TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- TEMP /u01/app/oracle/oradata/CDB/temp01.dbf SQL show user USER is SYS SQL SQL create temporary tablespace TMPE 2 tempfile /u01/app/oracle/oradata/CDB/temp02.dbf 3 SIZE 50M 4 AUTOEXTEND ON NEXT 50M 5 MAXSIZE 2048M 6 EXTENT MANAGEMENT LOCAL;-----创建临时表空间 SQL alter database default temporary tablespace TMPE;-----指定数据库的默认临时表空间 Database altered. SQL SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME DEFAULT_TEMP_TABLESPACE;------查看数据库默认的临时表空间 PROPERTY_VALUE -------------------------------------------------------------------------------- TMPEundo表空間重点1.一个事务只能将它的全部还原数据存放在同一个还原段中。但是多个并行的事务可以写一个还原段。每个还原段都有一个段头在该段头中包含了一个事务表该表中存放着有关使用这一还原段的当前事务信息。2.还原段的目的事务回滚事务恢复保证事务的一致性。3.还原段的类型系统还原段仅为系统表空间中的对象变化所用非系统还原段自动模式和手动模式为其他表空间中的对象变化所用包含私有还原段(单实例)和公有还原段RAC延迟还原段当脱机的表空间online时用到4.不能删除一个正在使用的undo表空间除非这个表空间没有活动的事务5.一个实例可以有多个undo表空间存在但是同时只能有一个活动的undo表空间SQL select name,value 2 from v$parameter 3 where name like%undo%;---------undo_retention的单位时秒 NAME VALUE ------------------------------ -------------------------------------------------- temp_undo_enabled FALSE undo_management AUTO undo_tablespace UNDOTBS1 undo_retention 900 SQL select tablespace_name,retention,contents 2 from dba_tablespaces 3 where tablespace_name likeUNDO%;------并不能保证undo段一定都能保存900s TABLESPACE_NAME RETENTION CONTENTS ------------------------------ ----------- --------------------- UNDOTBS1 NOGUARANTEE UNDO-------------如果将retention设置成guarantee此时如果某个事务需要还原段而又没有还原段oracle就不能覆盖哪些提交后需要保留的数据因此这个事务会执行失败。undo信息存放在undo段undo段只能放在undo表空间system表空间也会存放undo段undo表空间中不能包含其他任何对象。undo段中的数据可以保存多久呢分为inactive已经提交的事务和active未提交的事务。show parameter undo-----里面有一个undo_retention参数这个参数可以设置undo段中的数据可以保存的时间单位时秒。已经提交但是还没有达到保留时间的undo数据称为unexpired数据达到保留时间的数据成为expired数据expired数据oracle会自动清除unrxpired数据会自动保存但是如果undo段中的数据不够新事物用oracle也会把inactive的unexpired数据清除可以将表空间的数据设置成guarantee retention但是这有可能导致新事物的失败。SQL show parameter undo--------查看undo段信息保留的时间 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL select tablespace_name ,retention from dba_tablespaces;-----查看表空格键是否设置guarantee这里面可以看出是只有undo表空间才可以这样设置的。 TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY SYSAUX NOT APPLY UNDOTBS1 NOGUARANTEE TEMP NOT APPLY USERS NOT APPLY SQL alter tablespace undotbs1 retention guarantee;----这样设置之后autoextend的undo表空间依然会在新事物空间不够时清除inactive的undo数据但是fixd管理的undo段会把undo数据保留到设定时间再清除所以这可能会导致新事物的失败。 SQL select property_name,property_value,description from database_properties where property_name like%UNDO%;-----如果是CDB数据库可以通过这样的方式查看这个pdb使用的本地undo表空间还是共享undo表空间pdb的表空间如果是本地那么全部的pdb都是本地共享也是如此。 PROPERTY_NAME -------------------------------------------------------------------------------- PROPERTY_VALUE -------------------------------------------------------------------------------- DESCRIPTION -------------------------------------------------------------------------------- LOCAL_UNDO_ENABLED TRUE true if local undo is enabled閃回技術閃回版本查詢根據scn查詢某一個表在一段scn範圍内所作的修改閃回查詢查詢某一個scn時表的數據閃回事務查詢根據事務的xid在flashback_transaction_query 視圖查詢數據所作的更改然後可以執行與操作相反的sql語句還原數據的狀態閃回刪除回收站閃回表用戶不小先刪除了子表的數據子表的數據被刪除后主表的數據是可以存在的然後現在我想用閃回表技術閃回子表的數據但是我在執行這個操作的時候主表的數據也被刪除了那我這個閃回操作是不能執行成功的因爲閃回的子表數據在主表中找不到記錄。表的結構如果被更改也會閃回失敗Undo數據被覆蓋也會閃回失敗將表閃回到某一個時間點或者scn之前的狀態閃回數據歸檔原理是將原本存儲在undo表空間的數據存儲在一個永久表空間。閃回數據庫