Home » RDBMS Server » Backup & Recovery » Export with compress=y not working?
Export with compress=y not working? [message #246512] Thu, 21 June 2007 03:21 Go to next message
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"…………….

Re: Export with compress=y not working? [message #246528 is a reply to message #246512] Thu, 21 June 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem?

Regards
Michel
Re: Export with compress=y not working? [message #246530 is a reply to message #246512] Thu, 21 June 2007 04:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Export with compress=y not working? [message #246557 is a reply to message #246543] Thu, 21 June 2007 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Export with compress=y not working? [message #246570 is a reply to message #246557] Thu, 21 June 2007 06:08 Go to previous messageGo to next message
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.
  • Attachment: STORAGE.txt
    (Size: 1.83KB, Downloaded 188 times)
Re: Export with compress=y not working? [message #246573 is a reply to message #246570] Thu, 21 June 2007 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to have only one extent.
It is useless and leads to dangerous behaviour (like recreating the table).

Regards
Michel
Re: Export with compress=y not working? [message #246576 is a reply to message #246573] Thu, 21 June 2007 06:25 Go to previous messageGo to next message
paloma
Messages: 4
Registered: June 2007
Junior Member
OK. I thought I could get better performance if it is only one extent.
Re: Export with compress=y not working? [message #246594 is a reply to message #246576] Thu, 21 June 2007 07:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nope.

Regards
Michel
Previous Topic: Re: Open Database Error
Next Topic: Image copy and Backupset Using.
Goto Forum:
  


Current Time: Sun Dec 04 22:48:08 CST 2016

Total time taken to generate the page: 0.06772 seconds