OraFAQ Forum: Backup & Recovery » Export with compress=y not working?

Home » RDBMS Server » Backup & Recovery » Export with compress=y not working?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Export with compress=y not working? [message #246512]

Thu, 21 June 2007 03:21

paloma
Messages: 4
Registered: June 2007

Junior Member

Hi!

I am trying to export some tables with commpress=y but that dose not work. I don’t know if I am doing something wrong?
Oracle 9.2 on Windows
(sorry for my English)

SELECT EXTENTS, SEGMENT_TYPE, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME=’HANDELSELOGG’

EXTENTS SEGMENT_TYPE BYTES
---------------- -------------------------------- ------------
54 TABLE 40894464
1 row selected.

*****************************************************
desc HANDELSELOGG

VARCHAR2(36)
DATE
DATE
VARCHAR2(36)
VARCHAR2(36)
VARCHAR2(36)
NUMBER( Cool

******************************************************
exp user/pass@db TABLES=(HANDELSELOGG) COMPRESS=Y FILE=TEST.DMP

******************************************************
From test.dmp:
CREATE TABLE "HANDELSELOGG" ("ID" VARCHAR2(36) ……………………. PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST" LOGGING NOCOMPRESS
INSERT INTO "HANDELSELOGG"…………….

Report message to a moderator

Re: Export with compress=y not working? [message #246528 is a reply to message #246512]

Thu, 21 June 2007 03:58

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the problem?

Regards
Michel

Report message to a moderator

Re: Export with compress=y not working? [message #246530 is a reply to message #246512]

Thu, 21 June 2007 04:01

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

paloma

LOGGING NOCOMPRESS
INSERT INTO "HANDELSELOGG"…………….

I think he wanted to mention here as it is written no compress

[Updated on: Thu, 21 June 2007 04:01]

Report message to a moderator

Re: Export with compress=y not working? [message #246543 is a reply to message #246512]

Thu, 21 June 2007 04:55

paloma
Messages: 4
Registered: June 2007

Junior Member

Hi!

Table size = 40894464 BYTES
Extents = 54

When I export table with compress=y export file contains following sql:

CREATE TABLE "HANDELSELOGG" ("ID" VARCHAR2(36) ……………………. PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TEST" LOGGING NOCOMPRESS
INSERT INTO "HANDELSELOGG"…………….

That is wrong (I think). It should create table with initial extent = 40894464 not 65536.
I tried to drop table then import but it create to small table again with lots of extents.

Report message to a moderator

Re: Export with compress=y not working? [message #246557 is a reply to message #246543]

Thu, 21 June 2007 05:28

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

That is wrong (I think). It should create table with initial extent = 40894464 not 65536.

Why do you think it is wrong?
Post the storage parameters and all extent data of your original table.

Regards
Michel

Report message to a moderator

Re: Export with compress=y not working? [message #246570 is a reply to message #246557]

Thu, 21 June 2007 06:08

paloma
Messages: 4
Registered: June 2007

Junior Member

I maybe misunderstood compress parameter.
What I want to do is to reduce numbers of extents for all tables that have more then one, without recreating tables with script.

I supposed that following will do that:
1. export with compress
2. drop user cascade
3. coalesce tablespace
4. create user
5. import

I attached txt file with storage parameters for original table.