An empty table can have a definition in Oracle's dictionary before it has a segment. Its first insert can therefore be the first operation that needs physical storage. Creation privileges permit creating the object; the segment owner's tablespace quota controls the permanent space that owner may allocate.
Oracle DBA Lesson 69 Part 2 — Quotas at Segment Allocation
What is being allocated?
A table's segment is storage allocated to that table. An extent is a group of database blocks allocated to a segment. Those blocks belong to the tablespace's physical data files on disk.
An owner's quota applies to allocation in a particular permanent tablespace. It is not a row count or the sum of row payload sizes. Allocated extents include space for rows and segment overhead. Inserting another row into already allocated space need not require another extent. Growth that requires an extent needs sufficient allowance again.
A quota is also not a reservation of physical capacity. The tablespace and its data files must support the requested allocation. Temporary workspace has separate management.
See logical storage structures.
Read the owner's quota
The owner needs CREATE TABLE for tables in its own schema. Part 1 did not grant it. The quota belongs to the segment owner, even when a named operator authenticates the session. An ordinary owner session follows the same storage rule. Proxy relationships and authentication stay as they are.
UNLIMITED TABLESPACE overrides explicit quotas. The first insert below is the case of APP_OWNER with no allowance on the locally managed, online, read-write permanent tablespace USERS, with physical room for the small allocation, and with neither QUOTA_PROBE nor QUOTA_PROBE_NOW already present.
SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = 'APP_OWNER'
AND tablespace_name = 'USERS';BYTES is the space charged to the owner in that tablespace. MAX_BYTES is the quota in bytes, and -1 means an unlimited quota on that tablespace. Read a missing row together with the owner's grants. A missing row does not by itself establish the effective limit. Inspect UNLIMITED TABLESPACE separately. The current owner can read the same columns from USER_TS_QUOTAS.
See DBA_TS_QUOTAS and quotas and UNLIMITED TABLESPACE.
Follow deferred creation and the first insert
In the owner session:
CREATE TABLE quota_probe (id NUMBER)
SEGMENT CREATION DEFERRED
TABLESPACE users;For this eligible empty nonpartitioned heap table in a locally managed permanent tablespace, Oracle records the definition and postpones the segment. The explicit clause overrides the DEFERRED_SEGMENT_CREATION initialization parameter for this table. Oracle's 19c licensing table lists Deferred Segment Creation for Enterprise Edition, not Standard Edition 2. Quota and immediate-allocation principles remain useful in both editions.
Inspect the table:
SELECT table_name, segment_created
FROM user_tables
WHERE table_name = 'QUOTA_PROBE';
SELECT segment_name, tablespace_name, bytes
FROM user_segments
WHERE segment_name = 'QUOTA_PROBE';Before the first insert, SEGMENT_CREATED is NO, and USER_SEGMENTS has no row for the table segment.
INSERT INTO quota_probe VALUES (1);Oracle now needs a segment and checks the owner's allowance when allocating it. With no sufficient allowance and no overriding privilege, allocation fails. Creating the definition did not establish that the first allocation would succeed. Read the error. An insert can fail for a reason other than quota.
See CREATE TABLE, DEFERRED_SEGMENT_CREATION, and Deferred Segment Creation licensing.
Give the owner a bounded allowance
Changing the quota requires authority to alter the owner:
ALTER USER app_owner QUOTA 20M ON users;This assigns a twenty-megabyte quota on USERS. It does not preallocate or reserve twenty megabytes. Existing allocation and the actual extent size must fit within the quota.
Return to the owner session and retry:
INSERT INTO quota_probe VALUES (1);
ROLLBACK;With sufficient quota, privileges, and physical capacity, the insert creates the table segment. Rolling back the row does not remove the newly materialized segment. After the rollback, SEGMENT_CREATED is YES, and USER_SEGMENTS still has a row for the table.
Table and index segments use the quotas of their respective owners in the tablespaces that hold them. An index in another tablespace needs that owner's allowance there. Increasing the table's quota in USERS does not cover an index allocation in a different tablespace.
See ALTER USER.
Interpret allocation and limit together
Repeat the quota query. With one 64 KiB allocation and no other allocation for this owner in USERS:
| TABLESPACE_NAME | BYTES | MAX_BYTES |
|---|---|---|
| USERS | 65536 | 20971520 |
20M is 20 × 1,048,576 = 20,971,520 bytes. The 65,536-byte figure is that 64 KiB allocation. Block size, extent management, storage attributes, and existing objects can change the bytes actually allocated. Quota usage aggregates the owner's allocated segments in that tablespace.
An unlimited quota on one tablespace, shown by MAX_BYTES = -1, differs in scope from the UNLIMITED TABLESPACE system privilege, which overrides explicit quotas.
See CREATE USER.
Move allocation into CREATE TABLE
Using a different table in the owner session:
CREATE TABLE quota_probe_now (id NUMBER)
SEGMENT CREATION IMMEDIATE
TABLESPACE users;Oracle allocates the segment during this CREATE TABLE. The owner needs enough allowance, and the tablespace needs capacity, before any insert. USER_TABLES and USER_SEGMENTS for QUOTA_PROBE_NOW show that the segment already exists. Explicit IMMEDIATE overrides the deferred-creation parameter for the table.
| Situation | Point that needs allocation allowance |
|---|---|
Eligible empty table with explicit DEFERRED | First segment allocation, normally the first insert in this example |
Table with explicit IMMEDIATE | Segment allocation during CREATE TABLE |
| Existing segment that needs another extent | That later extent allocation |
| Insert fitting already allocated blocks | May need no additional segment allocation |
A zero or reduced quota does not drop existing tables or segments. A later row operation needs another quota check when it requires a new allocation.
See USER_TABLES and DBA_SEGMENTS.
Recap
An empty table can exist in the dictionary before it has a segment. SEGMENT CREATION DEFERRED on an eligible heap table records the definition and postpones the segment, overriding DEFERRED_SEGMENT_CREATION for that table. The first insert then allocates the segment. Oracle charges that allocation to the segment owner's quota on the tablespace that holds the segment, including when a named operator opened the session. CREATE TABLE does not grant the quota. DBA_TS_QUOTAS shows bytes already charged and MAX_BYTES. A value of -1 means an unlimited quota on that tablespace only. The UNLIMITED TABLESPACE system privilege overrides explicit quotas.
ALTER USER app_owner QUOTA 20M ON users sets a limit of 20,971,520 bytes on USERS. It does not reserve that space. The data files still need capacity, and a later extent needs allowance again. A row that fits in blocks already allocated need not take another extent. Rolling back the first successful insert removes the row and leaves the segment allocated. SEGMENT CREATION IMMEDIATE moves that first allocation into CREATE TABLE. Setting the quota to zero leaves existing segments in place. An index uses its owner's quota in the tablespace that stores the index.
No comments:
Post a Comment