-- STORAGE - Logical & Physical -- 1. Database -------------- Database --> Tablespace -- Tablespace : -- Within a database the tablespace is the largest logical storage structure -- Every database must consist of one or more tablespaces. -- Every tablespace must belong to one and only one database. -- Tablespace size = sum of data files size -------------- Database --> Datafiles -- Every Database must consist of one or more datafiles. -- Each datafile must belong to one and only one Database. -- 2. Tablespace ------------------------------------------ --------------- Tablespace --> Segments -- Segment : -- Within a tablespace, space is allocated to segments. A segment is an object in the -- database that requires storage, such as a table or an index. A view, on the other hand, -- is not a segment, since it does not store data; it is just a prewritten query that allows -- easy access to data stored in tables. -- Every tablespace may contain one or more segments. -- Every segment must exist in one and only one tablespace. ---------------- Tablespace --> Datafiles -- Every tablespace must consist of one or more datafiles. -- Each datafile must belong to one and only one tablespace. -- 3. Segments ---------------------------------------- ---------------- Segment --> Extents -- Extent : -- When space is allocated for segments in a tablespace, it is allocated in extent units. -- Every segment must consist of one or more extents. -- Each extent must belong to one and only one segment. ---------------- Segments >--> Datafiles -- Every datafile consists of one or more segments -- Each segmets can belong to one or more datafiles -- 4. Extents -------------- ------------- Extents --> Oracle blocks (Default 8k) -- Oracle block : -- Oracle block is the smallest logical storage unit -- for every Oracle I/O request at least one Oracle block is being read -- Every extent must consist of one or more Oracle blocks. -- Each Oracle block may belong to one and only one extent. ------------- Extents >-- Datafiles -- Every extent must be located in one and only one datafile. -- The space in the datafile may be allocated as one or more extents. -- Delete operations (with / without commit) won't change table High Water Mark (HWM) -- 5. Oracle blocks ------------------- ------------------ Oracle block --> OS blocks -- Every Oracle block must consist of one or more operating system blocks. -- Every operating system block may be part of one and only one Oracle block. -- It is a good idea to keep the operating system block size the same as the Oracle block size -- This way, for every Oracle I/O request the operating system needs to retrieve only one block. ------------------ Datafiles --> OS Blocks -- OS Block : -- Operating system blocks are the minimum allocation unit for the file system. -- Each file system has its own minimum and default size -- Every datafile must consist of one or more operating system blocks. -- Each operating system block may belong to one and only one datafile. -------------------------------- Schemas -- There is no relationship between a tablespace and a schema. -- Objects in the same schema can be in different tablespaces, -- and a tablespace can hold objects from different schemas. --------------------------------------------------------------------------------------------- -- Tablespace Types --------------------------------------------------------------------------------------------- ----------------------- The SYSTEM tablespace --------------------------------------------- -- * The SYSTEM tablespace contains the data dictionary-internal tables that describe -- the structure of the database itself, all of the objects in it, users, roles, and privileges. -- * The SYSTEM tablespace also contains the SYSTEM undo or rollback segment (also called SYSTEM), -- which can be used only for operations on objects stored in the SYSTEM tablespace. -- * Oracle Database creates The SYSTEM tablespace automatically when the database is created -- * User objects should be kept out of the SYSTEM tablespace in order to keep it -- operating efficiently. -- * Cannot be taken offline ----------------------- The SYSAUX tablespace (10g) --------------------------------------------------- -- * The SYSAUX tablespace is also considered a SYSTEM tablespace and is used to -- store statistical and other Oracle tools information (AWR, OEM and many more) -- * the SYSAUX tablespace is always created during database creation or database upgrade ----------------------- The Temporary tablespaces (9i) ------------------------------------------------------ -- * Temporary tablespaces are used mainly to manage space for database sort operations -- (Server Process will try to sort data in the PGA by default, if its not possible it -- will sort it in the Temporary tablespaces) -- * When the SYSTEM tablespace is locally managed, you must define at least one default temporary -- tablespace when creating a database. -- * A locally managed SYSTEM tablespace cannot be used for default temporary storage. -- * If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when -- creating the database, then SYSTEM is still used for default temporary storage. However, you -- will receive a warning in ALERT.LOG saying that a default temporary -- tablespace is recommended and will be necessary in future releases. -- * Cannot be taken offline ----------------------- The UNDO tablespaces -------------------------------------------- -- * Holds information that is used to roll back, or undo, changes to the database -- * A database can contain more than one undo tablespace, but only one can be in use at any time. -- * Cannot be taken Offline ----------------------- User Defined tablespaces ------------------------------------------------ -- * User Defined tablespaces -------------------------------------------------------------------------------- -- Viewing logical & physical storage using System Tables -------------------------------------------------------------------------------- ----------------------- -- Redo Log Files: SELECT * FROM v$log; SELECT * FROM v$logfile; -- Manual log switch: -- ALTER SYSTEM SWITCH LOGFILE; ----------------------- -- Tablespaces and Data files DESC DBA_TABLESPACES -- Information about all tablespaces in the DB: SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES; ------------------------ -- Information about all data files in the DB: DESC DBA_DATA_FILES col file_name for a50 SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB FROM DBA_DATA_FILES; ------------------------ -- Tablespace Size -- Allocated Size: SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME; -- Free Space: SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; -- Via OEM: -- Server => (Storage) => Tablespaces / Datafiles -------------------------------------------------------------------------------- -- In which Tablespace a new table will be created ? -------------------------------------------------------------------------------- -- Explicitly specifing the Tablespace CREATE TABLE newtab (id NUMBER) TABLESPACE users; -- If the Tablespace not explicitly specified, new table will be created in user -- Defalt Tablespace SELECT username, DEFAULT_TABLESPACE FROM dba_users; -- If there is no User Defalt Tablespace, it will be created in the -- Database Defalt Tablespace (10g) SELECT * FROM database_properties WHERE PROPERTY_NAME LIKE '%TABLESPACE%' ALTER DATABASE DEFAULT TABLESPACE example; -- If there is no Database Defalt Tablespace, the table will be created in the System Tablespace -------------------------------------------------------------------------------- -- Creating a new Tablespace -------------------------------------------------------------------------------- CREATE TABLESPACE MY_TBS DATAFILE 'c:\my_tbs_file1.dbf' SIZE 10M , 'c:\my_tbs_file2.dbf' SIZE 10M DROP TABLESPACE MY_TBS ; DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES ; CREATE BIGFILE TABLESPACE BIGFILE_TBS DATAFILE 'C:\BIGFILE.DBF'SIZE 100M ; DROP TABLESPACE BIGFILE_TBS INCLUDING CONTENTS AND DATAFILES ; -- Bigfile vs Smallfile ------------------------ -- * A bigfile tablespace contains only one datafile or tempfile, which can contain up to -- approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is -- 32TB (for a tablespace with 8K blocks). -- * A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or -- tempfiles, each of which can contain up to approximately 4 million (222) blocks. -- (32 GB for 8k block) -------------------------------------------------------------------------------- -- EXTENT MANAGEMENT (Of segments in a Tablespace) -------------------------------------------------------------------------------- -- Locally managed VS Dictionary-managed -- Dictionary managed --------------------- -- * Dictionary-managed tablespaces are the historical way of managing extents -- requires any allocation or deallocation of an extent to update a table in the data -- dictionary. -- * This means that any time an extent is allocated to a table, the data dictionary -- must be touched to record the change. When a table is dropped or truncated, the -- data dictionary must be changed. Because of the amount of work required on the data -- dictionary when using dictionary-managed extents, they are no longer recommended -- and exist primarily for backward compatibility. -- * All tablespaces should be created withlocal extent management CREATE TABLESPACE dict_tbs DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\dicttbs.dbf' SIZE 10m EXTENT MANAGEMENT DICTIONARY CREATE TABLE am05 (col1 number) STORAGE (initial 100mb next 10mb pctincrease 50); /* -- Dictionary managed Extent allocation INITIAL - size of the first extent (100) NEXT - size for the next extent (10) PCTINCREASE - (15) -- If System TBS is Locally managed, It's not possible to create DM TBS */ -- Locally managed ----------------------- -- * Locally managed extents are more efficient and are recommended for all database data -- * In a tablespace where extents are locally managed, free extents are stored in a bitmap in -- the tablespace -- * As an extent is allocated to a segment or freed up because a segment was -- dropped, truncated, or resized, the bitmap is updated to reflect the change CREATE TABLESPACE auto_tbs DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M EXTENT MANAGEMENT LOCAL -------------------------------------------------------------------------------- -- Locally managed - Extent allocation: AUTOALLOCATE vs. UNIFORM -------------------------------------------------------------------------------- -- AUTOALLOCATE, allocation method -- First 16 extents --> 64kb -- Next 63 extents --> 1MB -- Next 120 extents --> 8MB -- Ramaining extents --> 64MB -- DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES ; CREATE TABLESPACE auto_tbs DATAFILE 'C:\auto_tbs.dbf' SIZE 25M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES; col FILE_NAME for a40 COL TABLESPACE_NAME for a15 SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB FROM DBA_DATA_FILES; -- Create a table in the tablespace: CREATE TABLE auto_tab (a VARCHAR2(3000)) TABLESPACE auto_tbs; SELECT TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = 'AUTO_TBS'; -- See how much space is allocated to the table: col SEGMENT_NAME for a30 SELECT SEGMENT_NAME, BYTES/1024, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUTO_TAB'; -- See how many extents: DESC DBA_EXTENTS SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'AUTO_TAB'; -- Fill up the table with data: BEGIN FOR i IN 1..5000 LOOP INSERT INTO AUTO_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; -- Check segment size and extents again... -- How to drop Tablespace ------------------------- DROP TABLESPACE auto_tbs DROP TABLESPACE auto_tbs INCLUDING CONTENTS -- but what about the datafiles ? -- Creating the tablespace again with the [REUSE] option CREATE TABLESPACE auto_tbs DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\auto_tbs.dbf' SIZE 25M REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE; -- 1 [DROP TABLESPACE] and remove data files manually -- 2 DROP TABLESPACE auto_tbs INCLUDING CONTENTS AND DATAFILES -- CREATE TABLE [STORAGE] ------------------------- CREATE TABLE mytab2 (id number) STORAGE (INITIAL 100m); SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'MYTAB2'; DROP TABLE MYTAB2 Advantages ---------- -- 1. reduce I/O when the table is being scanned -- 2. Improve performance when Oracle allocates the extents -------------------------------------------------------------------------------- -- Local - UNIFORM SIZE -------------------------------------------------------------------------------- DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ; CREATE TABLESPACE uniform_tbs DATAFILE 'C:\uni_tbs.dbf' SIZE 25M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, NEXT_EXTENT / 1024 FROM DBA_TABLESPACES; -- Create a new table in it: CREATE TABLE uniform_tab (a VARCHAR2(3000)) TABLESPACE uniform_tbs; -- See how much space is allocated to the table: SELECT SEGMENT_NAME, BYTES/1024, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'UNIFORM_TAB'; -- See how many extents: col segment_name for a15 SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'UNIFORM_TAB'; -- Fill up the table with data: BEGIN FOR i IN 1..5000 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; -- See how many extents: SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'UNIFORM_TAB'; -- Other tablespaces: SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES; -- Check free space: SELECT TABLESPACE_NAME, BYTES/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'UNIFORM_TBS'; ---------------------------------------------------------------- -- RESIZE TABLESPACE: ---------------------------------------------------------------- -- Fill up: BEGIN FOR i IN 1..5000 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; / [ORA-01653: unable to extend table SYS.UNIFORM_TAB by 256 in tablespace] ------------------------------ -- 1. Add a new file: ------------------------------ -- Add a new file: ALTER TABLESPACE UNIFORM_TBS ADD DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' size 25m; ALTER TABLESPACE UNIFORM_TBS DROP DATAFILE 'C:\UNIFORM_TBS02.DBF' ; -- Fill up: BEGIN FOR i IN 1..5000 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size, AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth FROM dba_data_files WHERE TABLESPACE_NAME = 'UNIFORM_TBS'; -- Fill up again: BEGIN FOR i IN 1..5000 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; ------------------------------------ -- 2. Increase an existing datafile: ------------------------------------ --------------------------------- -- 2.1 - Manual Resize - RESIZE --------------------------------- ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 50M; ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' RESIZE 30M; SELECT FILE_NAME, BYTES/1024/1024 File_Size, MAXBYTES/1024/1024 Max_Size, AUTOEXTENSIBLE, INCREMENT_BY*8/1024 Growth FROM dba_data_files WHERE TABLESPACE_NAME = 'UNIFORM_TBS'; SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 size_mb, COUNT(*) files FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME; BEGIN FOR i IN 1..5000 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; / ------------------------------------ -- 2.2 Automatic resize - AUTOEXTEND ------------------------------------ ALTER DATABASE DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNIFORM_TBS02.DBF' AUTOEXTEND ON NEXT 10m MAXSIZE 150m; ---- DROP TABLESPACE uniform_tbs INCLUDING CONTENTS AND DATAFILES ; CREATE TABLESPACE uniform_tbs DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\uni_tbs.dbf' SIZE 25M AUTOEXTEND ON NEXT 10M ; CREATE TABLE uniform_tab (a VARCHAR2(3000)) TABLESPACE uniform_tbs; SELECT SEGMENT_NAME, BYTES/1024, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'UNIFORM_TAB'; col segment_name for a15 SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'UNIFORM_TAB'; BEGIN FOR i IN 1..7500 LOOP INSERT INTO UNIFORM_TAB VALUES (LPAD('*',3000,'*')); END LOOP; END; / SELECT SEGMENT_NAME, EXTENT_ID, BYTES/1024, BLOCKS FROM DBA_EXTENTS WHERE segment_name = 'UNIFORM_TAB'; col FILE_NAME for a40 COL TABLESPACE_NAME for a15 SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 File_Size_MB FROM DBA_DATA_FILES; ---------------------------------------- -- ONLINE/OFFLINE READ ONLY/READ WRITE ---------------------------------------- SELECT tablespace_name, status FROM dba_tablespaces; -- ONLINE/OFFLINE ----------------- -- You may want to take a tablespace offline for any of the following reasons: -- To make a portion of the database unavailable while allowing -- normal access to the remainder of the database -- To perform an offline tablespace backup -- (even though a tablespace can be backed up while online and in use) -- To rename or relocate tablespace datafiles -- When a tablespace is taken offline, the database takes all the associated files offline. -- * You cannot take the following tablespaces offline: -- SYSTEM -- The undo tablespace -- Temporary tablespaces ALTER TABLESPACE example OFFLINE; ALTER TABLESPACE example ONLINE; -- READ ONLY/READ WRITE ----------------------- ALTER TABLESPACE example READ ONLY; SELECT last_name from hr.employees WHERE employee_id = 100; UPDATE hr.employees SET salary=salary; ALTER TABLESPACE example READ WRITE ; -------------------------------------------------------------------------------- -- OMF Oracle-Managed Files -------------------------------------------------------------------------------- -- Oracle-Managed Files ----------------------- -- Using Oracle-managed files simplifies the administration of an Oracle Database. -- Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system -- files comprising an Oracle Database. -- You specify operations in terms of database objects rather than filenames. -- The database internally uses standard file system interfaces -- to create and delete files as needed. -- Enabling the Creation and Use of Oracle-Managed Files -------------------------------------------------------- -- DB_CREATE_FILE_DEST -- This initialization parameter allow Oracle use the Oracle-managed files for Datafiles -- and Temporary files show parameter db_create_file ALTER SYSTEM SET db_create_file_dest = 'E:\app\Owner\oradata\orcl'; CREATE TABLESPACE tbs_3 ; -- By default, OMF Files are 100MB, Autoextentsible (unlimited) DROP TABLESPACE tbs_3 ; CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M; DROP TABLESPACE tbs_3 ; CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M ; DROP TABLESPACE tbs_3 ; CREATE TABLESPACE tbs_3 DATAFILE SIZE 40M, SIZE 20M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; ALTER TABLESPACE tbs_3 ADD DATAFILE; DROP TABLESPACE tbs_3 ; -- Is it still possible to use Non-OMF? CREATE TABLESPACE mytbs DATAFILE 'C:\oracle\mytbs.dbf' SIZE 10m -- Advantages: -------------- -- DBAs don't need to specify file names, locations and sizes when creating a tablespace or database -- Automatic removal of files when a tablespace or log file is dropped -- Simplified creation of test and development systems -- Disadvantages: ----------------- -- Can only be used with file systems, not with RAW Volumes -- Generated file names and locations might not be according to the site's naming standards -- Limited scope for file placement and I/O tuning may impact performance -- (although locations can be altered dynamically)
