Oracle Database Administration

1. Database files Logical database structures Oracle Database Administration

2. Data storage structures • Physical structures: • Data files – disk files that contain database data • Logical database structures include: • Tablespaces • Segments • Extents • Blocks

3. Data files • Data files: • all database data is stored there • data files can automatically extend if required (the AUTOEXTEND clause) • one or more datafile form a logical unit of database storage called a tablespace • Oracle does not write modified data immediately, if the database is not closed properly, datafiles can contain incorrect data

4. Data files • Data file: • Can be regular file on a hard drive (any hard drive in a computer) (typical case) • Can be a raw device (on Unix systems). Oracle writes data directly to disk with minimum overhead) • Can use external hard drives (can be used when configuring Real Application Cluster)

5. Tablespaces • Tablespace: • basic logical storage unit – table data, index data is stored in a tablespace • consists of one or more datafile (each datafile belongs to exactly one tablespace) • there is no relationship between tablespaces and schemas: • objects from one schema can exist in different tablespaces • one tablespace can contain objects from different schemas • to create tablespace: CREATE TABLESPACE new_tablespace DATAFILE 'C:\...\new_tab.dbf' SIZE 10M • to put table in a tablespace: CREATE TABLE (id number) TABLESPACE new_tablespace;

6. Tablespaces • Each database has special tablespaces: • SYSTEM tablespace – should only be used by system objects • SYSAUX tablespace – Oracle managed internal data • temporary tablespace – used by temporary tables and temporary data – sorting, groupping • undo tablespace – used to store undo data required for transaction rollback

7. Tablespaces • Each user has default tablespace and temporary tablespace: • CREATE USER xxx IDENTIFIED BY yyy DEFAULT TABLESPACE zzz TEMPORARY TABLESPACE vvv; • Default tablespace is where user objects are created unless otherwise specified • Temporary tablespace is where temporary objects will be stored: • user’s temporary table data • temporary space required by operations like sorting, grouping, distinct

8. Temporary tables • Temporary table example: CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER ) ON COMMIT DELETE ROWS; CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER ) ON COMMIT PRESERVE ROWS;

9. Temporary tables • Temporary tables are used to store data for a duration of a session or transaction • One session never sees temporary data from other session • Data is automatically deleted when session disconnects, table is TRUNCATED

10. Tablespaces • Tablespace size is the size of all its datafiles • Objects assigned to a tablespace store their data in tablespace datafiles: • single object can span multiple datafiles belonging to the same tablespace • when all tablespace datafiles become full Oracle: • tries to extend a datafile created with the AUTOEXTEND clause with size smaller than MAXSIZE • reports an error if no file has AUTOEXTEND clause or files already have their MAXSIZE

11. Adding more space to the database • To add more space do one of: • add new file to existing tablespace: • ALTER TABLESPACE name ADD DATAFILE ‘path’ SIZE 1G AUTOEXTEND OFF • add new tablespace: • CREATE TABLESPACE name DATAFILE ‘path’ SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 10G • change size of existing datafile: • ALTER DATABASE DATAFILE ‘path’ SIZE 4G

12. Adding more space to the database • Adding datafile to a tablespace

13. Adding more space to the database • Adding new tablespace

14. Adding more space to the database • Increasing datafile size

15. Putting objects in a tablespace • When creating object: • CREATE TABLE table_name (column definitions) TABLESPACE tablespace_name • CREATE INDEX index_name ON table_name(column_list) TABLESPACE tablespace_name • When moving object: • ALTER TABLE table_name MOVE TABLESPACE tablespace_name • ALTER INDEX index_name REBUILD TABLESPACE tablespace_name • When the table is moved all indexes become unusable • unusable index needs to be rebuild before it can be used: ALTER INDEX index_name REBUILD

16. Putting objects in a tablespace

17. Temporary tablespaces • Creating temporary tablespace requires different syntax: CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

18. Segments • Segment: • is a space allocated for a specific data structure, e.g. each table has its own data segment, each index has index segment • segment is always stored in a single tablespace (possibly in multiple files from that tablespace) • each segment is divided into extents and extents are divided into data blocks

19. Segments • Example: • Table with 2 indexes has 1 table segment, and 2 index segments • Each of those segments can be stored in a different tablespace • Information about segments is in the USER_SEGMENTS and DBA_SEGMENTS views

20. Extents • Extent: • is a contiguous space in a data file • when adding data to a segment Oracle tries to fill existing extents, if all extents are full – Oracle adds another extent • extents of a segment may not be contiguous on disk – multiple extents usually mean that table data is fragmented

21. Data blocks • Data block: • smallest unit of data allocation • database block size is usually between 2KB – 32KB

22. Segments, extents and data blocks

23. Data blocks • Data block: • smallest unit of data allocation • database block size is usually between 2KB – 32KB

24. Free space in a data block • Oracle inserts multiple rows into single data block (if row size is smaller than block size) • Sometimes a row will occupy more than one block: • if a row size is larger than data block size – data chaining • if a row is inserted small and then updated and there is no space left in the data block – data migration • Data chaining is unavoidable, Oracle tries to prevent data migration

25. Data migration example • CREATE TABLE test1 (id NUMBER, text VARCHAR2(4000)); • INSERT INTO test1 (id, text) VALUES (1, NULL); • INSERT INTO test1 (id, text) VALUES (2, NULL); • INSERT INTO test1 (id, text) VALUES (3, NULL); • INSERT INTO test1 (id, text) VALUES (4, NULL); • INSERT INTO test1 (id, text) VALUES (5, NULL); • -- all rows are inserted into single data block • UPDATE test1 SET text = ‘some long text......’ WHERE id IN (1, 2, 4, 5, 6); • -- there is no space left in the row for all these rows, • -- Oracle performs data migration

26. Data migration • When performing data migration Oracle: • moves row data to another data block • keeps pointer to the new data block in the original data block • Scanning multiple data blocks to get one row decreases performance – data migration should be prevented • Two parameters are provided to control free space in the block: PCTFREE and PCTUSED

27. PCTFREE • Example: • CREATE TABLE test (id NUMBER) PCTFREE 20; • PCTFREE – amount of space in the block reserved for future updates of the inserted rows

28. PCTUSED • Example: • CREATE TABLE test (id NUMBER) PCTFREE 20 PCTUSED 40; • PCTUSED – once free space in the block drops below PCTFREE, oracle uses free space in the block only for updates until amount of used space drops below PCTUSED

29. PCTUSED and PCTFREE

30. Extent allocation • Extent allocation strategy is important for performance and free space management: • Many small extents decrease performance – segment is fragmented • Too large extents waste space • Extents within a tablespace can be managed: • LOCALLY – (recommended) information about extents is stored in tablespace itself • DICTIONARY – information about extents is stored in data dictionary in SYSTEM tablespace

31. Extent allocation • -- Default extent management • CREATE TABLESPACE tsh_data_1 • DATAFILE 'c:\Oracle\Oradata\TSH1\tsh101.dbf' SIZE 50M; • -- Explicit dictionary extent management • CREATE TABLESPACE tsh_data_2 • DATAFILE 'c:\Oracle\Oradata\TSH1\tsh201.dbf' SIZE 50M • EXTENT MANAGEMENT DICTIONARY; • -- Local extent management using autoallocate • CREATE TABLESPACE tsh_data_3 • DATAFILE 'c:\Oracle\Oradata\TSH1\tsh301.dbf' SIZE 50M • EXTENT MANAGEMENT LOCAL AUTOALLOCATE; • -- Local extent management using uniform extents • CREATE TABLESPACE tsh_data_4 • DATAFILE 'c:\Oracle\Oradata\TSH1\tsh401.dbf' SIZE 50M • EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;