Home » RDBMS Server » Server Utilities » space problem when impoting (oracle 10.2.0.1.0,Redhat Linux 4)
space problem when impoting [message #433601] Thu, 03 December 2009 04:24 Go to next message
goracle9
Messages: 136
Registered: December 2006
Senior Member
sorry i could not format as i m unaware
SQL> show user
USER is "SYSTEM"
SQL> /

TABLESPACE       Totalspace(MB) Used Space(MB) Freespace(MB)    % Used    % Free
--------------- --------------- -------------- ------------- --------- ---------
BADEEL                     1024              0          1024         0       100
HRADMIN                      10              0            10         0       100
IBTIKAR                    2048              0          2048         0       100
INDX                       3698           2484          1214     67.17     32.83
MCPP                        600             50           550      8.33     91.67
PARTS                      8650           5497          3153     63.55     36.45
SALES                       621            299           322     48.15     51.85
SERVICE                    4059           2702          1357     66.57     33.43
SYSAUX                     5430           2299          3131     42.34     57.66
SYSTEM                      700            499           201     71.29     28.71
UNDOTBS1                   4900             51          4849      1.04     98.96
USERS                       500            206           294      41.2      58.8

12 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  CREATE USER badeel IDENTIFIED BY badeeldev
  2         DEFAULT TABLESPACE badeel
  3         TEMPORARY TABLESPACE temp
  4*        QUOTA 1000m ON badeel
SQL> /

User created.

SQL> grant connect to BADEEL;

Grant succeeded.

SQL> conn badeel/[email]badeeldev@dev[/email]
Connected.
SQL> desc user_ts_quotas
 Name                            Null?    Type
 ------------------------------- -------- ----
 TABLESPACE_NAME                 NOT NULL VARCHAR2(30)
 BYTES                                    NUMBER
 MAX_BYTES                                NUMBER
 BLOCKS                                   NUMBER
 MAX_BLOCKS                               NUMBER
 DROPPED                                  VARCHAR2(3)

SQL> select tablespace_name,bytes from user_ts_quotas;

TABLESPACE_NAME                    BYTES
------------------------------ ---------
BADEEL                                 0

SQL> select tablespace_name,bytes,max_bytes from user_ts_quotas;

TABLESPACE_NAME                    BYTES MAX_BYTES
------------------------------ --------- ---------
BADEEL                                 0 1.049E+09


size of export file is
----------------------

-rw-r--r--   1 devora oinstall  488K Dec  3 10:42 srv.dmp

importing into badeel now
----------------------------

imp system/[email]develop@develop[/email] file=srv.dmp log=srv.log rows=n fromuser=srvadmin touser=badeel



TABLESPACE_NAME                    BYTES MAX_BYTES
------------------------------ --------- ---------
BADEEL                         1.049E+09 1.049E+09

my quesstion is my export dump file size 488k, exported without data (rows=n), imported in to

badeel how it takes 1000 mb full in badeel tablespace and still not imported all objects
with error IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace 'BADEEL'

why this mutch space this taking??

experts please explain.

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 03 December 2009 05:18] by Moderator

Report message to a moderator

Re: space problem when impoting [message #433614 is a reply to message #433601] Thu, 03 December 2009 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
goracle9 wrote on Thu, 03 December 2009 11:24
sorry i could not format as i m unaware

Unaware of what? Many Forum members successfully format their post, so I believe that you are capable of learning how to do that too. Therefore, read "How to format your post?" section in the OraFAQ Forum Guide

I'll apply [code] tags for you, this time. Please, learn how to do that before posting your next message.
Re: space problem when impoting [message #433625 is a reply to message #433601] Thu, 03 December 2009 05:39 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can use the dba_segments view to find out what is using the space :

select  OWNER,
        SEGMENT_NAME,
        SEGMENT_TYPE,
        TABLESPACE_NAME,
        BYTES
  from  dba_segments
  where TABLESPACE_NAME = 'BADEEL'
  order by OWNER, SEGMENT_NAME
Re: space problem when impoting [message #433700 is a reply to message #433601] Thu, 03 December 2009 13:00 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
goracle9 wrote on Thu, 03 December 2009 05:24

my quesstion is my export dump file size 488k, exported without data (rows=n), imported in to

badeel how it takes 1000 mb full in badeel tablespace and still not imported all objects
with error IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace 'BADEEL'

why this mutch space this taking??


Size of export file is almost irrelevant to amount of space object take up after import. You can have an export of 2 tables with no data that takes up hundreds of gigabytes upon import.

Many reasons, one being that your LMT uniform extent size is very large.
Re: space problem when impoting [message #433997 is a reply to message #433625] Sun, 06 December 2009 05:45 Go to previous messageGo to next message
goracle9
Messages: 136
Registered: December 2006
Senior Member
AS I IMPORTED OBJECTS FROM SRVADMIN TO BADEEL

SELECT OWNER,SEGMENT_TYPE,TABLESPACE_NAME,sum(bytes/1024/1024)"size"
FROM dba_segments WHERE OWNER IN('BADEEL','SRVADMIN')
GROUP BY OWNER,SEGMENT_TYPE,TABLESPACE_NAME ORDER BY OWNER

THE RESUL IS:-


OWNER                          SEGMENT_TYPE       TABLESPACE_NAME                     size
------------------------------ ------------------ ------------------------------ ---------
BADEEL                         INDEX              BADEEL                             680.5
BADEEL                         TABLE              BADEEL                          1301.625
SRVADMIN                       INDEX              INDX                             562.125
SRVADMIN                       INDEX              SERVICE                           96.375
SRVADMIN                       TABLE              SERVICE                         1260.125

SRVADMIN TAKES 1918.625 M
BADEEL TAKES 1982.125

NEARLY 64M DIFFERENCE IS IT OK?? WHY ITS TAKE 64M MORE

PLEASE EXPLAIN.
Re: space problem when impoting [message #433999 is a reply to message #433997] Sun, 06 December 2009 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please don't post in UPPER case.
Please don't use code tags for your text just for code and what is needed to be aligned.

Regards
Michel
Re: space problem when impoting [message #434001 is a reply to message #433999] Sun, 06 December 2009 06:24 Go to previous message
goracle9
Messages: 136
Registered: December 2006
Senior Member
as i m imported object from srvadmin to badeel

SELECT OWNER,SEGMENT_TYPE,TABLESPACE_NAME,sum(bytes/1024/1024)"size"
FROM dba_segments WHERE OWNER IN('BADEEL','SRVADMIN')
GROUP BY OWNER,SEGMENT_TYPE,TABLESPACE_NAME ORDER BY OWNER


result is

OWNER                          SEGMENT_TYPE       TABLESPACE_NAME                     size
------------------------------ ------------------ ------------------------------ ---------
BADEEL                         INDEX              BADEEL                             680.5
BADEEL                         TABLE              BADEEL                          1301.625
SRVADMIN                       INDEX              INDX                             562.125
SRVADMIN                       INDEX              SERVICE                           96.375
SRVADMIN                       TABLE              SERVICE                         1260.125
srvadmin takes 1918.625 M space
badeel takes 1982.125 M

why it takes 64m more space
please explain
Previous Topic: Doubts abt user data
Next Topic: Flashback queries.
Goto Forum:
  


Current Time: Mon Sep 26 11:10:51 CDT 2016

Total time taken to generate the page: 0.18854 seconds