| space problem when impoting [message #433601] |
Thu, 03 December 2009 04:24  |
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 #433625 is a reply to message #433601] |
Thu, 03 December 2009 05:39  |
ThomasG
Messages: 3211
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 |
joy_division
Messages: 4963
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 |
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 #434001 is a reply to message #433999] |
Sun, 06 December 2009 06:24 |
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
badeel takes 1982.125 M
why it takes 64m more space
please explain
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
