Creating objects without STORAGE clauses

In some teams it's usual not to indicate neither storage clauses nor tablespace clauses, so I've gotten some interest in studying the consecuences. Here are the results with a single table in Oracle 10GR2:

I create a single table with no clauses:

create table sense_clausules (col varchar(2000)) tablespace PL_DATOS;

col segment_name for a30
col INITIAL_EXTENT for 999,999
col next_extent, bytes for 999,999
select segment_name, INITIAL_EXTENT, next_extent, bytes
from dba_segments
where segment_name=upper('sense_clausules')
order by 1;


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 65536

Next I load a lot of rows:


set serveroutput on
declare i number;
j number;
begin
for j in 1..100000 loop
for i in 1..1000 loop
insert into sense_clausules values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') nologging;
end loop;
commit;
dbms_output.put_line(j*1000);
end loop;
end;
/


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 75.497.472

select count(1) from sense_clausules;

COUNT(1)
------------
1.998.010

select sum(bytes)
from dba_extents
where segment_name=upper('sense_clausules')
order by 1;

SUM(BYTES)
------------
148.897.792

And now the quantity and size of the extensions after the load:

col segment_name format a30
select segment_name,bytes, count(*)
from dba_extents
where segment_name=upper('sense_clausules')
group by segment_name,bytes
order by 2,3;

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 6291456 1
SENSE_CLAUSULES 8388608 9

4 files seleccionades.

Now a more heavy load and observe:


COUNT(1)
------------
100.000.000

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 8388608 120
SENSE_CLAUSULES 35651584 1
SENSE_CLAUSULES 67108864 94

5 files seleccionades.

294 extensions created for 100 milions rows.

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 148.897.792


drop table sense_clausules cascade constraints;

I, personally, prefer to indicate the INITIAL and NEXT clauses in the way the capacity plan indicates.

Comparing several schemas with dbms_space.object_space_usage

This is useful, for example, if we import or refresh a schema to another instance.
In this example we'll compare schemas DWH03 and PRUEBA03.

set serveroutput on

DECLARE
CURSOR C IS select OWNER, SEGMENT_NAME, SEGMENT_TYPE,100, PARTITION_NAME
from dba_segments
where (OWNER LIKE 'PRUEBA03' OR OWNER LIKE 'DWH03')
and segment_name not like 'BIN%'
AND PARTITION_NAME IS NULL
and SEGMENT_TYPE in ('TABLE', 'INDEX', 'CLUSTER')
group by owner, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME
order by owner, SEGMENT_TYPE, SEGMENT_NAME;

F C%rowtype;
SPACE_USED NUMBER;
SPACE_ALLOCATED NUMBER;
TOTAL1 NUMBER;TOTAL2 NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(999999999);
open c;
fetch c into f;
TOTAL1:=0; TOTAL2:=0;
LOOP
EXIT WHEN C%NOTFOUND;
dbms_space.object_space_usage(F.OWNER, F.SEGMENT_NAME,
F.SEGMENT_TYPE,100, F.PARTITION_NAME,SPACE_USED,
SPACE_ALLOCATED);
if F.OWNER='DWH03' THEN TOTAL1:=TOTAL1+SPACE_USED;
elsif F.OWNER='PRUEBA03' THEN TOTAL2:=TOTAL2+SPACE_USED;
end if;
dbms_output.put_line
(F.OWNER','F.SEGMENT_TYPE','F.SEGMENT_NAME','F.PARTITION_NAME','SPACE_USED','SPACE_ALLOCATED);
fetch c into f;
END LOOP;
close c;
dbms_output.put_line('SUMA DE DWH03 = 'TOTAL1);
dbms_output.put_line('SUMA DE PRUEBA03 = 'TOTAL2);
END;
/

MOVING OBJECTS TO NEW TABLESPACES WITH IMPDP

The scenario consists in having about 180 tablespaces and wish to move the objects to a set of 10 news tablespaces (based on the table size criteria for every schema). The “alter table…move…” technique generates thousands of individual orders and is very difficult to trace. For that reason I've used impdp and a few orders easy to follow for anyone.

1) Export the schemas to reorganize:
expdp system schemas=dwh01, dwh02 directory=exports dumpfile=dwh01-2_dp_1.dmp, ...
logfile=dwh01-2_dp.log content=all keep_master=N parallel=3 filesize=25G

2) Create new tablespaces.

3) Create new users (we can't rename it) or drop and recreate the existing ones

4) Import one schema at a time: impdp system PARFILE=dwh01_P.par

Example.par:
------------
directory=exports
dumpfile=dwh01-2_dp_1.dmp, dwh01-2_dp_2.dmp, ...
logfile=dwh01_import_P.log
content=all
keep_master=N
parallel=4
REMAP_SCHEMA=DWH01:PRUEBA01
EXCLUDE=PACKAGE
EXCLUDE=PACKAGE_BODY
EXCLUDE=FUNCTION
REMAP_TABLESPACE=AWCUBO:DWHTSDATP01,...
REMAP_TABLESPACE= ...,
TABLES=DWH01.LKBACAMPO01,...

5) Recreate invalid or unusable indexes

6) Recompile

7) Check the quantity of objects between source and destination

select owner, count(table_name)
from dba_tables
where (OWNER LIKE 'PRUEBA0_' OR OWNER LIKE 'DEMETRIO0_' OR OWNER LIKE 'D__0_')
group by owner
order by 1;