Skip to Main Content
  • Questions

  • export/import from dictionary to local managed database

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 16, 2001 - 8:54 am UTC

Last updated: October 29, 2007 - 11:03 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi, Tom.

I want to reorganize the customer Oracle database from dictionary storage to local managed TS with uniform size. The problem is that all users object are in one TS and have a great difference in size. Most objects/tables are very small (about 1000 objects < 1MB) and some are very large (20 objects > 100MB, one is more than 1GB). This database was emailed to me as export file and I cannot change anything in export file.
If I attempt to import this file in one local managed TS with uniform size I have a problem to choose the extent size. When extent size to small (1MB), Oracle cannot allocate by import enough extents for my large tables because of maxextents 121 clause for this tables.
When extent size to big (10-20MB) to be able to allocate more than 1GB for the largest table and not to offend against the 121 maxextents restrictions of this table, Oracle allocates 10-20 MB for initial extent of each small table of only few rows and have a problem with the size of tablespace: By extents size 20MB Oracle needs more than 20GB for 1000 small objects.
To solve this problem I want to create two tablespace with different extent size: one for small objects (uniform extent size 0,5MB) and another one for big objects with 50MB uniform extent size.
My questions is:
1. How can I sort my tables in export file by size and than import them in two different TS depend of the table size?
2. I have all users objects in original database in TS USERS. In my new database I have created two TS BIGOBJECTS and SMALLOBJECTS. By import I want to avoid the creations of USER TS in my new database and creation of any users objects in system TS. How can I do this?
3 Is that right, that by import of full database Oracle first attempts to create a tablespaces as in original database and put all users objects in the same TS as in original database, than if Oracle can not create this TS it puts the user objects in default TS of this user.
If I pre create user with default TS different from original database, how can I avoid that full database import overwrite my user or make system to default TS of this user when TS USERS not exist in new database.

In addition I have some more questions to understand my problems:

By import Oracle attempt to do the next (The export was made from dictionary managed TS with COMPRESS=Y):

CREATE TABLE PCTFREE 1" "0 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE (INITIAL 61440 NEXT 368" "64 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1" " BUFFER_POOL DEFAULT) TABLESPACE "USERSX""

4: What of this objects/TS storage parameters from old dictionary managed TS (initial, next, maxextents) are relevant for my new LM TS?
5 . Where is MAXEXTENTS 121 clause from? Is this default of Oracle? It was not define maxextents 121 by creating of object in original database.
6 Is there any possibility to overwrite this maxetents clause from export in my new database?
7 Can different small tables share one extent in TS?
8 Would recommend to use autoalllocate clause to import objects of very different size in one TS? What makes Oracle to allocate initial extent of 1 GB (export with compress=Y) in those TS? Make it this one 1 GB too?

Olaf


and Tom said...

1) you can do an

imp userid=x/y full=y INDEXFILE=tables.sql

that'll just read the dmp file and create a ddl file for you. You can get all of the create tables from here.

Edit that file, delete the storage clauses and just change the tablespaces to be one of the two you want.

Delete all other statements in there (create indexes and such, we just want the create tables).

Now, run that DDL to pre-create the tables.

Now, you can run IMP userid=x/y full=y IGNORE=y

the ignore option lets the import continue on when an object it wanted to create already exists. It'll load right into your tables.


2) in order to avoid any stray objects getting into system -- you will:

a) revoke UNLIMITED TABLESPACE from your account (in case it has it, resource and dba both grant this priv directly to you).

b) setup your default tablespace

c) import. import will rewrite the create using your default tablespace. This works for everything EXCEPT multi-tablespace objects (tables with LOBS, partitioned tables, index organized tables with OVERFLOW statements). Those objects must be created by hand first (but since you pre-created all of the tables -- this is not an issue)

3) that is correct, it would try to put it back into the same tablespace however, you've precreated the tables so that won't happen, it'll just use your tables.



4) just delete the storage clauses from the create tables. You don't want them at all (well, maybe you want to retain pctfree/pctused). Just use the LMT storage allocation

5) maxextents is the old default based on an 2k blocksize (before we had unlimited extents). Objects in LMT's all have unlimited extents -- it is ignored:

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int ) tablespace users storage ( maxextents 5 );

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select table_name, max_extents from user_tables
2 where table_name = 'T';

TABLE_NAME MAX_EXTENTS
------------------------------ -----------
T 2147483645


6) won't need to

7) if you CLUSTER them but it only makes sense to CLUSTER them in special cases. Read about clusters (i have a pretty good section on them in my book). In your case -- I doubt it.

8) i don't like autoallocate personally. I only use uniform. Oracle would look at the 1gig initial request and allocate a couple of extents to satisfy that. Again, I would suggest wacking the storage clause all together from the DDL and just let it allocate uniformly sized extents as needed.



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A possibility for the poster...

Connor, October 16, 2001 - 12:10 pm UTC

Rather then indexfile, it can sometimes be very useful to use 'strings' (unix) on an "exp rows=n" dump file - mainly because the create commands are not broken over multiple lines. This makes removing the storage clause (with something like sed 's/STORAGE(.*)//g' easier.

good tip

Andrew, November 02, 2001 - 12:38 pm UTC

Connor - thanks for the useful tip

We can generate the script by using powerdesign tools

jacky, June 02, 2003 - 10:48 am UTC

We can generate the script by using powerdesign tools, then import the data imp userid=accela/aaaaa@orcl full=y ignore=y indexes=n

dmt to lmt

karthik, September 17, 2003 - 7:22 am UTC

please tell me what r the thing i should consider before importing data from a dmt to lmt , is it compulsary to remove the storage clause.
thanks in advance
karthik

Tom Kyte
September 17, 2003 - 8:07 am UTC

compulsary?
no

good idea?
probably


it'll take the storage clause, figure out HOW MUCH would have been allocated in the DMT and allocate the same amount in the LMT. if that is what you want -- thats OK, if not, remove the storage.

For CLOB

Reader, September 17, 2003 - 9:11 am UTC

Is there a link that I can refer to see how to import tables with CLOB datatype? Thanks.

Tom Kyte
September 17, 2003 - 11:51 am UTC

umm, it is just "imp"

nothing special for import.

the server Utilities guide documents import.



exp dictionary tables to LMT

Jozef, May 12, 2004 - 5:24 pm UTC

Hi Tom, what would be the best scenario to export all objects from DB , about 7,000 of them into LMT.
I need to change to uniform size of LMT and I would like to have storage clause removed from objects(tables,indexes).
I can not afford to edit "indexfile" to 7,000 objects.
Right now DB 40GB, is fragmented and I must reorganize everything. Any tools that will let me create script for create script to create indexes for all tables without storage clause before imp?

Tom Kyte
May 13, 2004 - 9:36 am UTC

don't use export/import.

use


alter table T MOVE storage (initial 1k next 1k minextents 1 pctincrease 0) tablespace whatever;

alter index i rebuild storage (initial 1k next 1k minextents 1 pctincrease 0) tablespace whatever;


you can do that nologging, parallel, whatever (slightly faster than exp/imp). You won't stand a chance of accidently "losing something". and the alters are pretty trivial to generate right out of the data dictionary itself with a simple select.


reader

A reader, August 16, 2005 - 11:58 am UTC

Here is an exerpt from 9,2 Database Administration Guide:

<>
Specifying Segment Space Management in Locally Managed Tablespaces
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL

Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

AUTO

This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
<>

Is it true that

All LMT segments use bitmap to track to record space
usage in blocks. Then what is the significance of freelist
in manual alloaction (uniform allocation)

Also according to the manual "manual is default". If
allocation type (uniform or autoallocate) is not specified
during tablespace creation, I found LMT defaults to SYSTEM
allocation of extents.



Tom Kyte
August 17, 2005 - 10:52 am UTC

LMT's use a bitmap at the head of the file to track the EXTENTS in the tablespace.

ASSM (automatic segment space management) uses bitmap blocks in the extents to manage the use of the blocks.


LMTS -> bitmaps to manage extents.
ASSM -> bitmaps to manage blocks in the extents.


Manual Segment Space Management (that is the manual they refer to) is default today - but 10gr2 changes that.


System allocated extents, not UNIFORM (what are not "manual" but uniform) are the default for LMTs if not specified

reader

A reader, August 16, 2005 - 12:32 pm UTC

Never mind about the previous posting

There was a confusion between

EXTENT MANAGEMENT {DICTIONARY |
LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }
SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}


reader

A reader, August 17, 2005 - 12:37 pm UTC

Therefore ASSM has bmb which replaces the freelists.
In not ASSM, it has freelists. Am I correct

Tom Kyte
August 17, 2005 - 1:58 pm UTC

ASSM has bit map blocks and no freelists (there is no such thing as a "bitmap freelist")

Manual segment space management uses freelists and freelist groups.

reader

A reader, August 17, 2005 - 2:04 pm UTC

Thanks
I meant bmb (Bit Map Blocks levels 1,2,3) replaces
freelists

Tom Kyte
August 17, 2005 - 2:08 pm UTC

correct, i only mentioned "bitmap freelists" because I've seen people using this term which is made up and doesn't make sense.

Not that you used it!

What happens to pctfree/pctused in ASSM

A reader, September 26, 2005 - 5:41 pm UTC

Looking at the default settings in dba_tables for ASSM created tablespaces, Oracle chooses the default 10/40 pctfree/pctused parameters.

Are these settings still used for the BMB fillfactor, and if so, how?

Tom Kyte
September 27, 2005 - 10:01 am UTC

the only one that counts is pctfree - and it defaults to 10%. pctfree is obeyed as normal (for future updates)

pctused has no meaning for ASSM managed storage. It is a legacy value, maintained in the event you move that segment to manual segment space management.

Question about size of uniform extent

paola, February 08, 2006 - 11:07 am UTC

I want to create a TS that is going to have a big objects. I want to make it local uniform. My quetion is: Is it OK if I make the inform size 300M ? Like I said, I have tables of 10G... so it would have 34 extents. But I don´t know if is there any limit to the size of the uniform eztent, maybe 300M is to big.

I've alredy created the TS (I can still erase it), and I see there is 100M used... but I did not create any object. Can you explain me Why is that?

Thank you.

Tom Kyte
February 08, 2006 - 11:20 am UTC

are you sure there isn't "300m" used.

when you create the tablespace, you must add 64k (or 128k if you have a 32k blocksize) to the datafile. eg: datafile would be of size N*300mb + 64k

If it is N*300mb, we take 64k for ourselves - but you then have a 299.99mb extent which won't work (you said 300mb) so it is "wasted".

To get it back, grow your datafile by 64k.


By using a 300mb extent, you have to understand that is your MINIMAL unit of allocation. If a table is 300.00001mb it will consume 600mb of space.

If you do parallel 12 operations (say a direct path load), you will have AT LEAST 12 * 300mb of space - regardless of how much data you load. And if you load 300.0001 * 12 mb of data (so each load thread gets 300.0001 mb) you will have 24*300mb of allocated space (with 12 almost empty extents)

If you used system allocated extents, we are able to extent trim - so that the last extent is only as big as it needs to be.


Therefore, it is a tradeoff in space vs number of extents.


using system allocated extents your 10g object would have about 300 or so extents, perfectly reasonable, acceptable, ok, nothing to be afraid of, good, positive, not negative, <any other good ok word you want>.




Question about size of uniform extent

Paola, February 08, 2006 - 11:49 am UTC

You're rigth! I've just recreated the test TS of total size of 7002MB and uniform size extent of 100M. Only 2 MB appears to be used.
You told me: 300 extents is not to much for a table, so How many extents is bad?

I don't want to use autoallocate because I lost control over utilization of space. Do you think a 100M size of extent could it be suit?


Tom Kyte
February 09, 2006 - 4:11 am UTC

you would have to have many thousands/tens of thousands of extents before I'd say "rethink that" and since a 10g object takes a couple of hundred and beyond 10g I'm thinking about using partitioning anyway.... I don't really hit it.


I gave you the pros and cons - with uniform size, you have uniform size and everything it implies. No extent trimming, expect to waste lots of space in the last extent - especially if you do parallel operations (whereby you have N last extents - where N was the degree of parallelism).

It is entirely your choice.

DMT-LMT

Ranga, March 10, 2006 - 4:55 am UTC

It is very good!!!!!!!!

Stuart, October 26, 2007 - 7:09 am UTC

Hi Tom,

Earlier on in the question, you said use the alter table move syntax for moving between tablespaces, however this doesn't work on table objects with type log or long raw. So I'm going to use exp/imp.

Do I need to worry about the compress argument to exp when moving to a LMT with auto allocation and auto segment management?

Stu
Tom Kyte
October 29, 2007 - 11:03 am UTC

do not use compress. pre-create the table without a storage clause and let it do it itself.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.