Tag: deffered segments

Database, Tools

Segment Creation on Demand or Deferred Segment Creation

 , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Segment Creation on Demand or Deferred Segment Creation

I also talked about it in post EXP Missing Tables on 11.2.

Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.

More“Segment Creation on Demand or Deferred Segment Creation”

Database, Errors and Bugs, SQL | PLSQL

ORA-01950 On Insert but not on Create Table

 , , ,

Sounds weird creating table does not raise any error, but inserting a correct tuple in this table raise a permission error, right? Just take a look:

SQL> create table matheusdba.table_test(a number) tablespace TEST_TABLESPACE;
Table created.
SQL> insert into matheusdba.table_test values (1);
insert into matheusdba.table_test values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TABLESPACE'

More“ORA-01950 On Insert but not on Create Table”

Backup and Restore, Database, Errors and Bugs, Tools

EXP Missing Tables on 11.2

 , ,

Made an exp and some table is missing, right? The database is 11.2+? The tables missing have no rows in source dabase, right? Bingo!
This happen because Oracle implemented a space saving feature on 11.2 called Deffered Segment Creation.

This feature basically makes that the first segment of a table is only allocated when the first row is inserted. It was implemented because Oracle realized is not rare to find databases with lots of tables that haven’t ever had a row.

The situation occurs because the EXP client uses dab_segments as index to exporting, and, this feature makes that no segment be allocated. For Oracle, it’s not a problem, considering the use of Datapump (EXPDP/IMPDP).

But (there always exist a “but”), let’s suppose you have to export the file to a different location not accessible by directory nor has local space, or either, your table has a long column (yes, it’s deprecated, I know… but let’s suppose this is a legacy system…). Then, you can do:

1) For all tables that has no rows, allocate an extent:
alter table owner.tabela allocate extent;

To generate, the script:

select 'alter table '||owner||'.'||table_name||' allocate extent;' from all_tables where num_rows=0;

2) Export using clausule VERSION=11.1 or lower on EXP. 🙂

More about Deffered Segment Creation: https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2

Hope It helped.
See ya!
Matheus.