370 likes | 586 Views
Database files Logical database structures. Oracle Database Administration. Data storage structures. Physical structures: Data files – disk files that contain database data Logical database structures include: Tablespaces Segments Extents Blocks. Data files. Data files:
E N D
Database files Logical database structures Oracle Database Administration
Data storage structures • Physical structures: • Data files – disk files that contain database data • Logical database structures include: • Tablespaces • Segments • Extents • Blocks
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
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)
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;
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
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
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;
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
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
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
Adding more space to the database • Adding datafile to a tablespace
Adding more space to the database • Adding new tablespace
Adding more space to the database • Increasing datafile size
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
Temporary tablespaces • Creating temporary tablespace requires different syntax: CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;
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
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
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
Data blocks • Data block: • smallest unit of data allocation • database block size is usually between 2KB – 32KB
Data blocks • Data block: • smallest unit of data allocation • database block size is usually between 2KB – 32KB
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
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
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
PCTFREE • Example: • CREATE TABLE test (id NUMBER) PCTFREE 20; • PCTFREE – amount of space in the block reserved for future updates of the inserted rows
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
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
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;