Initial Parameter Tables

[thiagomz]

Let's go to the problem ...

Next ... I received a 300mb dump, I did the import and when I went, the
Boy had used 6GB !!!! Checking the cause, I noticed a few parameters
initial tables with 600MB, and these tables almost do not have
lines, that is, the programmer deleted lines of that table, but the
initial Continues to mark your last value? Correct my reasoning?

Now comes the problem, such as hitting the initial of these tables, so that I can
to export this data to a Dump for the programmer to create
system structure On customers?

An Alter Table ... Move Tablespace would hit the table size
by removing the marked lines to delete with the delete command, but the initial
would still remain, right ?

Thanks ...

- Att. Thiago M. Zerbinato OCP DBA Oracle Thiagomz@hotmail.com (MSN) 75311127 (ICQ)

[dr_gori]

I will quote the words of the chiappa in relation to this:

Good, precisely for questions of the IMHO type who ** has ** to create tables in the bank ** Always ** is the DBA (using the growth and behavior predictions of the table that analysts / programmers will pass) ... including this is more for problem that problem, because (as we know, and the manual of Concepts also explains us) Initial ** is not ** processing result, it is an initial indication -> even if you enter data in the table, at the exact instant of CREATE TABLE The BD already allocates on disk the initial, if you have an initial of 600 MB , this means that before the same data is entered the table already consumed 600 MB ... It is hardly justifiable, it is almost always error.

As the initial is only allocated when from the beginning of the table, for you to change you have to recreate it: Yes, this can be done dropping and recreating, or with Alter Table Move, which also re-allocates all data from it, functioning as a recreation (and obviously one must indicate the desired tablespace, the Storage clause with the initial and NEXT, indicating freelists, PCTFree and PCTUSED, Normal and routine DBA work that was not done when the table originally was created).

Once changed the table will maintain space allocation characteristics (initial, next, etc.), and future exports will respect, as long as the compress = n clause is used. If you still have not imported, to this .dmp you have in hand, the solution is laborious, you would have to import with the Rows = N option, make the moves of the desired tables (and rebuild of the indexes, if there are), and then Re-import with ignore = y.

[] S

Chiappa

I cite also Ederson Elias de Oliveira

Good morning Thiago,

See excerpt from a DMP of mine:

Select all

CREATE TABLE "CA_FIGURA" 
("ID" NUMBER(12, 0) NOT NULL ENABLE,  
"IM_FIGURA" LONG RAW NOT NULL ENABLE,  
"TP_FIGURA" VARCHAR2(10) NOT NULL ENABLE,  
"NO_EMPRESA" VARCHAR2(100))   
PCTFREE 10 PCTUSED 40 INITRANS 1  
MAXTRANS 255  
STORAGE(INITIAL 40960 FREELISTS 1 FREELIST GROUPS 1)  
TABLESPACE "D_COMERCIALIZACAO" LOGGING NOCOMPRESS

Observe the initial parameter, in the example is 40960 (40KB).

When does not specify this parameter in the creation of the table, it is set as the default of the tablespace, you need to verify what is the current value being set, to see the need to always provide this Value in the creation of tables.

In the case of a table creation, if this value has erroneously scaled, the table will be allocated to the tablespace with that initial, occupying DB space without need if that the lines to be loaded Not yet inserted, or if the calculation was wrong and the space will not be used.

To correct this failure, it is best to create another table with the correct initial value and insert the original rows into the new table.

In this case the wrong initial table should be dropped, but rather to check which indexes, constraints, views, triggers and procedures depend on the table. Knowing all this, drop the table with wrong initial and rename the new table to the original name and recreate / recreate the invalid objects.

Another way is also generating an Export with Rows = N and edit the DMP by removing the lines before the Create Table, and changing the Create, removing the Storage clause, also removing the export lines CREATE TABLES, and run it as SQL by SQLPLUS. In this way the default value of the Tablespace destination and the initial value will be correct if the error is in the initial table in the original base.

After this, do the export of the original base and import, with the clause Ignore = Y, in this way it will be ignored the creation of existing objects, and only the lines and other objects will be imported (procedures, triggers, functions, constrain and indexes, etc.).

OK?


Ederson Elias de Oliveira
DBA Oracle

[thiagomz]

I did this how to ... I still have not tested ... but I think this is correct ...

How to hit the initial parameter of tables
======= =============================
Objective
] =========

Setting the initial parameter before performing an import of a Dump that
contains an initial with high value.

Procedure
============

1. Dump Import with the Rows = N
Ex: Imp System / Manager @ TNSNAME FROMUSER = User Touser = User ROWS = N

2. After this we must move the tables to another tablespace or move
on the same tablespace, for this to do in SQL * plus


verify that the C: \ Move file .sql This correct and the execute,
This will hit the initial parameter of all specified tables and
has also executed a rebuild of the indicates.

Select all

@c:\move.sql;

3. Now we must import dump data, then do:

Select all

   Ex: imp system/manager@tnsname fromuser=usuario touser=usuario IGNORE=Y

4. Make a new dump.

[thiagomz]

Personnel,

The final version was like this:

How to hit the initial parameter of tables
========== ================================= [99]
Objective
= ========

Setting the initial parameter before performing an import of a Dump that
contains an initial with high value.

Procedure
============

1. Dump Dump with the Rows = N

Select all

   Ex: imp system/manager@tnsname fromuser=usuario touser=usuario ROWS=N

option. After this we must move the tables to another tablespace or move in

same tablespace, for this to do in SQL * plus

Select all

   2.1 - set pagesize 0 
   2.2 - spool c:\move.sql 
   2.3 - SELECT 'alter table '||owner||'.'||table_name||' move  
tablespace '||tablespace_name||' ; 'FROM dba_tables WHERE owner = 'USUARIO' 
   2.4 - spool off

2.5 - Check If the C: \ Move.sql file is correct and the execute,
this will hit the initial parameter of all specified tables.

Select all

   2.6 - @c:\move.sql;

3. Now we must import dump data, then do:

Select all

   Ex: imp system/manager@tnsname fromuser=usuario touser=usuario IGNORE=Y

4. Make a new dump.

5. Make a regions rebuild.
Ex:

Select all

select 'alter index ' || INDEX_NAME || ' rebuild ;' from dba_indexes where owner='USUARIO';