Table space data file (1)

1. Create a tablespace: CreatetablespacefelixDatafileu01apporacleoradatafelixfelixtbs. tablespace;

1. Create a tablespace: Create tablespace felix Datafile/u01/app/oracle/oradata/felix/felixtbs. dbf Size100m autoextendonnext10m maxsize1024m external tablespace; this is very important. How to view the created

1. Create a tablespace

Create a tablespace:

Create tablespace felix

Datafile '/u01/app/oracle/oradata/felix/felixtbs. dbf'

Size100m autoextendonnext10m maxsize1024m

Extentmanagementlocaluniformsize128k

Segmentspacemanagementauto;

It is very important to use the following statement to view the created tablespace attributes:

Selecttablespace_name, block_size, contents, extent_management, allocation_type, segment_space_management

From dba_tablespaces

Where tablespace_name = 'Felix ';

To view the extended properties of a tablespace, use dba_data_files:

Selecttablespace_name, autoextensible, increment_by, maxbytes

Fromdba_data_files

Where tablespace_name = 'Felix ';

-- Create a user for the Application

Createuser felix identifiedby felix

Defaulttablespace felix

Temporarytablespace temp;

# You can query the user information in the DBA_USERS view:

Selectusername, user_id, password, default_tablespace, temporary_tablespace

Fromdba_users

Whereusername = 'Felix'

# Grant the following permissions:

Grantconnect, resourceto felix;

Revokeunnlimited tablesapce from felix;

Alteruser felix quotaunlimitedon felix;

Ii. Table space management technology

(1) data dictionary table space management technology (DMT): the so-called data dictionary management table space refers to when an object is created or deleted, oracle's tablespace distribution or recovery is recorded and managed through the data dictionary in the database. The two data dictionaries used for management are:UET $ (used extents, used space) and FET $ (free extents, idle tablespace ).

SQL> DESC UET $;

Name Type Nullable Default Comments

--------------------------------------

SEGFILE # NUMBER

SEGBLOCK # NUMBER

EXT # NUMBER

TS # NUMBER

FILE # NUMBER

BLOCK # NUMBER

LENGTH NUMBER

SQL> DESC FET $;

Name Type Nullable Default Comments

-----------------------------------

TS # NUMBER

FILE # NUMBER

BLOCK # NUMBER

LENGTH NUMBER

You can clearly see information such as FILE # And BLOCK # To manage space allocation and recovery;

How data dictionary manages tablespaces. When a new segment or segment requests a new space in the tablespace, oracle executes a series of SQL statements to complete the operation, these operations include finding available free space from the FET $, moving or adding or removing corresponding rows to UET $, and deleting corresponding records in the FET $; When deleting a segment, oracle moves the corresponding line in UET $ to FET $. This process is continuous and serial. In busy databases, such operations may lead to competition and waiting, generate data dictionary contention; on the other hand, when the information of the data dictionary table is modified, the system also needs to record undo and redo information, frequent modifications inevitably affect the performance of the entire database;

However, another problem facing data dictionary tablespace management is space fragmentation.

(2) Local tablespace Management Technology (LMT ):Oracle no longer uses data dictionary management, but adds a bitmap area to the data file header of each tablespace to record the usage of each extent. Each time an extent is used, or oracle will record this record with the new data file header when it is released for reuse, reflecting this change;

The creation syntax is as follows:

CREATE TABLESPACEtablespace_name

DATAFILE 'datafile _ path_name'

[Extent manasgement {LOCAL | AUTOALLOCATION | UNIFORM [SIZEINTER [K | M]}];

Because the range (extent) is the smallest allocation unit when an oracle object is created, the tablespace management is actually the interval management;

-- Query the table space type through the DBA_TABLESPACES View:

Select tablespace_name, extent_management, allocation_type

From dba_tablespaces;

DBA_EXTENTS records the range (EXTENT) allocated to each object, the space allocated to which objects, and the file where the range is located:

SQL> SELECTEXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'ts _ test ';

EXTENT_ID BLOCK_ID BLOCKS

------------------------------

0 536 8

1 544 8

2 552 8

3 560 8

4 568 8

5 576 8

6 584 8

7 592 8

8 600 8

9 608 8

......

11 624 8

12 632 8

13 640 8

14 1800 8

15 1808 8

16 768 128

17 896 128

18 1024 128

19 1152 128

20 1280 128

21 1408 128

56 7040 128

57 7168 128

58 rowsselected

Dump data block information:

Select object_id,

Dbms_rowid.rowid_relative_fno (rowid) file #,

Dbms_rowid.rowid_block_number (rowid) block #

From ts_test

Where rownum: <= 100;

Dump:

# Alter systemdumpdatafile3blockmax 1 blockmin 6;

Selectvaluefrom v $ diag_info;

[Oracle @ felix ~] $ Vi/U01/app/oracle/diag/rdbms/felix/trace/felix_ora_3219.trc

* ** 00:53:38. 498

Block 1 (file header) not dumped: use dump file header command

Block dump from cache:

Dump of buffer cache at level 4 for tsn = 2 rdba = 12582914

BH (0x6dbf97a8) file #: 3 rdba: 0x00c00002 (3/2) class: 13 ba: 0x6db88000

Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 25

Dbwrid: 0 obj:-1 objn:-1 tsn: 2 afn: 3 hint: f

Hash: [0x77fb7be0, 0x77fb7be0] lru: [0x6dbf9e80, 0x6dbf9760]

Lru-flags: hot_buffer

Obj-flags: object_ckpt_list

Ckptq: [0x6a3ed9a8, fingerprint] fileq: [0x6c7e74c8, 0x6bfe75f8] objq: [fingerprint, 0x6dbf9788] objaq: [0x6dbf9eb8, 0x6dbf9798]

St: XCURRENT md: NULL tch: 13

Flags: buffer_dirty block_written_once redo_since_read

LRBA: [0x14. 4b5f. 0] LSCN: [0x0. 14de1c] HSCN: [0x0. 14de27] HSUB: [1]

Block dump from disk:

Buffer tsn: 2 rdba: 0x00c00002 (3/2)

Scn: 0x0000.00145380 seq: 0x02 flg: 0x04tail: 0x53801d02

Frmt: 0x02 chkval: 0x1352 type: 0x1d = KTFBBitmapped FileSpace Header

Hex dump of block: st = 0, typ_found = 1

Dump of memory from 0x00007FAD09B98200 to0x00007FAD09B9A200

7FAD09B98200 492a21 00C00002 0014538004020000 [...... S ......]

7FAD09B98210 00001352 00000003 0000000800003700 [R ......]

7FAD09B98220 00000009 00000280 003FFFFE0000007E [......]

7FAD09B98230 000036FF 00000060 000004DA00145375 [. 6... '......]

7FAD09B98240 00000000 00000000 0000000000000000 [......]

Alter session set events 'immediate trace name file_hdrs level 10 ';

1 select file_id, extent_id, block_id, blocks

2 from dba_extents

3 * where segment_name = 'ts _ Test'

FILE_ID EXTENT_ID BLOCK_ID BLOCKS

----------------------------------------

4 0 536 8

4 1 544 8

4 2 552 8

4 13 640 8

4 14 1800 8

4 15 1808 8

4 16 768 128

4 17 896 128

4 18 1024 128

4 19 1152 128

4 20 1280 128

4 21 1408 128

4 22 1536 128

4 23 1664 128

4 24 2944 128

4 25 3072 128

4 26 3200 128

4 56 7040 128

4 57 7168 128

4 58 7296 128

59 rows selected.

SQL> select block_size, tablespace_name, min_extents, max_extents fromdba_tablespaces;

BLOCK_SIZE TABLESPACE_NAME MIN_EXTENTS MAX_EXTENTS

--------------------------------------------------------------

8192 SYSTEM 1 2147483645

8192 SYSAUX 1 2147483645

8192 UNDOTBS1 1 2147483645

8192 TEMP 1

8192 USERS 1 2147483645

8192 EXAMPLE 1 2147483645

8192 STATSPACK 1 2147483645

8192 STATSPACKTEMP 1

8192 FELIX 1 2147483645

9 rows selected

Section space management technology:

The Unit is block for space use and management;

The main sections are of the following types:

SQL> select distinct (segment_type) fromdba_segments;

SEGMENT_TYPE

------------------------------------

LOBINDEX

INDEX PARTITION

TABLE SUBPARTITION

TABLE PARTITION

NESTED TABLE

ROLLBACK

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

CLUSTER

TYPE2 UNDO

12 rows selected.

(1) manual Segment space management (manual Segment space management (Practices before 9iThe Free List (pctlist) allocated by the field header is used to manage block usage. Simply put, the Free List is considered as a data table, oracle relies on a series of algorithms to add or remove blocks from the Free List to manage segments;

(2) autosegment space management: management through bitmap

The major advantage of ASSM is that bitmap groups can reduce the burden of Bufferbusy wait, which was a serious problem in earlier 9i versions and significantly improves concurrency, because different parts of the bitmap array can be used at the same time, this eliminates the serialization of searching for the remaining space.

First, the first two data blocks are the data file headers ~ The eight data blocks are bitmap blocks, and the next 9th and 10th are ASSM bitmap blocks;

Dump 9th blocks:

Alter database dump datafile 3 block 9;

SQL> alter systemdump datafile 3 block 9;

System altered.

SQL> select value from v $ diag_info;

VALUE

Bytes ------------------------------------------------------------------------------------

TRUE

/U01/app/oracle

/U01/app/oracle/diag/rdbms/felix

/U01/app/oracle/diag/rdbms/felix/trace

/U01/app/oracle/diag/rdbms/felix/alert

/U01/app/oracle/diag/rdbms/felix/incident

/U01/app/oracle/diag/rdbms/felix/cdump

/U01/app/oracle/diag/rdbms/felix/hm

/U01/app/oracle/diag/rdbms/felix/trace/felix_ora_3050.trc

0

0

11 rows selected.

Tail-300/u01/app/oracle/diag/rdbms/felix/trace/felix_ora_3050.trc

* ** 02:07:30. 355

Block dump from cache:

Dump of buffer cache at level 4 for tsn = 2 rdba = 12582921

Block dump from disk:

Buffer tsn: 2 rdba: 0x00c00009 (3/9)

Scn: 0x0000. 000f2557 seq: 0x01 flg: 0x04tail: 0x25571e01

Frmt: 0x02 chkval: 0xc075 type: 0x1e = KTFBBitmapped File Space Bitmap

Hex dump of block: st = 0, typ_found = 1

Dump of memory from 0x00007FAD0893C600 to0x00007FAD0893E600

7FAD0893C600 running a21e 00C00009 000F255704010000 [...... W % ......]

7FAD0893C610 109c075 00000003 002E808000000000 [u ......]

7FAD0893C620 00000000 109f800 0000000000000000 [......]

7FAD0893C630 00000000 00000000 0000000000000000 [......]

Repeat 507 times

7FAD0893E5F0 00000000 00000000 20171000025571e01 [.......... W %]

File Space Bitmap Block:

BitMap Control:

RelFno: 3, BeginBlock: 3047552, Flag: 0, First: 0, Free: 63488

0000000000000000 0000000000000000 00000000000000000000000000000000

0000000000000000 00000000000000000000000000000000 0000000000000000

0000000000000000 00000000000000000000000000000000 0000000000000000

To query the segment header, you can use the dba_segment View:

SQL> selectsegment_name, header_file, header_file, header_block from dba_segments

2 where segment_NAME = 'ts _ test ';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

-----------------------------------------------------

TS_TEST 4 4 538