-- 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)
