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