asked on

Database Size

I just install the database from the scratch and select the most common installation instructions. It creates a database by default. Now I make a user and import data into just one table from a .csv files using sql* loader. All goes well.This process is now continuing and The data goes on increasing and now it contains 10 million rows ( the size of dump file is 600MB).

I am not a dba so no idea about "what is tablespace and like these concerns". But i am worring if I continue this way, Is there some drawbacks in proceeding this way. Can there be a limit of data in this scanario.

I find some queries which give me the following results.

My worries starts from here ::

1* create index idx_reg on mytab(region)
SQL> /
create index idx_reg on mytab(region)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

SQL> select count(*) from mytab;

COUNT(*)
----------
10265178

SQL> select tablespace_name, bytes from dba_data_files;

TABLESPACE_NAME BYTES
------------------------------ ----------
SYSTEM 1011875840
UNDOTBS 209715200
CWMLITE 20971520
DRSYS 20971520
EXAMPLE 159907840
INDX 26214400
TOOLS 10485760
USERS 26214400

8 rows selected.

select substr(d.tablespace_name,1,25) TABLESPACE,
to_char(sysdate,'fmMonth,ddth,yyyy') "TODAY",
D.FILE_ID FILE_ID,
D.BYTES/1024/1024 TOT_MBs,
d.bytes/vp.value ORACLE_BLKS,
sum(e.blocks) TOT_USED,
round(sum(e.blocks)/(d.bytes/vp.value),4) *100 PCT_USED
from sys.dba_extents e,
sys.dba_data_files d,
v$parameter vp
where d.file_id = e.file_id (+)
and vp.name ='db_block_size'
group by d.tablespace_name, d.file_id, d.bytes,vp.value
/

TABLESPACE FILE_ID TOT_MBS ORACLE_BLKS TOT_USED PCT_USED
--------------- ---------- ---------- ----------- ---------- --------
CWMLITE 3 20 5120 1520 29.69

DRSYS 4 20 5120 1968 38.44

EXAMPLE 5 152.5 39040 38976 99.84

INDX 6 25 6400

SYSTEM 1 965 247040 243481 98.56

TOOLS 7 10 2560 1472 57.50

UNDOTBS 2 200 51200 4918 9.61

USERS 8 25 6400

8 rows selected.

Can Someone have solutions to my worries.

penzk001

It seems that the problem is originating from the temporary tablespace, check out the default temporary space of the user you are using. Note that if the temporary tablespace was created as a real temporary tablespace using the tempfile clause (8i and above), it will not be shown in the dba_data_files but in dba_temp_files. You may be lacking space in this the tempspace, or you may have a quota say 200M on the tablespace and the create index is requiring more then 200M.

Use the dba viedba_users to determine the temporary tablespace of the user, and for quotas use dba_ts_quotas.

penzk001

oops the list sentence is as follows:
Use the dba views, dba_users to determine the temporary tablespace of the user, and dba_ts_quotas for determine the quota size..

Nievergelt

I would suggest to use

SELECT * FROM DBA_TABLESPACES;

to list all tablespaces.

hyynes

ASKER

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
SYSTEM 4096 12288 12288 1
UNDOTBS 4096 65536 1
CWMLITE 4096 65536 1
DRSYS 4096 65536 1
EXAMPLE 4096 65536 1
INDX 4096 65536 1
TEMP 4096 1048576 1048576 1
TOOLS 4096 65536 1
USERS 4096 65536 1

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
----------- ------------ ----------
249 50 0
2147483645 65536
2147483645 65536
2147483645 65536
2147483645 65536
2147483645 65536
0 1048576
2147483645 65536
2147483645 65536

I split the results into two lines.....

Any help..What should I do ....

Nievergelt

Please post what you get from
SELECT * FROM DBA_TEMP_FILES;

penzk001

Run the following:

SQL> select USERNAME , DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
from dba_users
where
username = 'USR1';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ----------------------------
USR1 TOOLS TEMP

SQL> select USERNAME, MAX_BYTES, TABLESPACE_NAME
from dba_ts_quotas
where USERNAME = 'USR1';

USERNAME MAX_BYTES TABLESPACE_NAME
------------------------------ ---------- ------------------------------
USR1 -1 TOOLS
USR1 -1 TEMP

SQL>

This shows that user USR1 does not have a quota limit. If your that a limit (value not -1), login as system, and either grant unlimited tablespace on temp or increase the quota;

SQL> alter user USR1 quota unlimited on temp;

User altered.

SQL>

or you can grant the resource.

sQL> grant resource to USR1;

Grant succeeded.

SQL>
with either one the problem should be solved. Now log in back as the user and retry the index .. this time it should work.

If the fails again please post the select suggested by Nievergelt, as it will imply that the temporary tablespace does not have enough space to contain the required temporary segments.

helpneed

hi
See the dynamic performance views V$SORT_USAGE and V$SORT_SEGMENT for more
information regarding the usage of space within these temporary segments.
V$SORT_USAGE will tell you who's using what.

First of all, make sure that the user has 'TEMP' as the TEMP
tablespace in the user profile. To do this, type:

ALTER USER username TEMPORARY TABLESPACE temp;

To find out if you DO need more space in the "temp" tablespace, issue following query
while running the million record query, preferably several times during the execution and right before the ORA-1652 error:

SELECT b.file_name, a.tablespace_name, a.bytes, a.blocks FROM dba_free_space a, dba_data_files b WHERE a.file_id = b.file_id AND a.tablespace_name = 'TEMP';

The above query will tell you how much contiguous free space is contained on each datafile. If the free space keeps declining during your query,and eventually runs out of space, then you can conclude that you need to add another datafile to the "temp" tablespace.Do not add another tablespace as you suggested, because the new tablespace will not be used in the query. Instead, add yet another datafile to your "temp" tablespace.

regards

Mehul Shah

Check the size of the temp tablespace. Fire the below query. It seems that the temp tablespace size is not enough and you might have to increase the temp tablespace size.

SELECT sum(bytes/1024/1024) "Size in MB" FROM DBA_TEMP_FILES

hyynes

ASKER

Nievergelt you need:
SELECT * FROM DBA_TEMP_FILES;
gives me the results

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
D:\ORACLE\ORA9I\ORAD 1 TEMP 41943040 10240 AVAILABLE 1 NO 0 0 0 40894464 9984
ATA\ORCL\TEMP01.DBF

But still I confused what to do, Does you peoples tell if I do nothing and go as same scenario what will be the problems and when it should expected ????

helpneed

hi

thats what if u didnt check for proper size these error will be commin again

using the above query check for contigious free space on each datafile. If the free space keeps declining during your query,and eventually runs out of space, then you can conclude that you need to add another datafile to the "temp" tablespace.

so in this case the same space problem will happend again if not properly checked...

dont get confused just check free space is there before running the query and if so run it and if not add one more datafile to the tablespace

hope this is clear for u

regards

hyynes

ASKER

>>dont get confused just check free space is there before running the query and if so run it and if not add one more datafile to the tablespace

How to accomplish this ... Tell me how can I find the free space. i run the following query and get the following results.

SQL> select * from user_FREE_SPACE;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM 1 242938 98304 24 1
SYSTEM 1 243907 266240 65 1
SYSTEM 1 326393 32768 8 1
SYSTEM 1 242650 86016 21 1
SYSTEM 1 243316 983040 240 1
UNDOTBS 2 337 786432 192 2
UNDOTBS 2 6417 44040192 10752 2
UNDOTBS 2 17425 1048576 256 2
UNDOTBS 2 17937 29360128 7168 2
UNDOTBS 2 25361 8388608 2048 2
UNDOTBS 2 27665 96403456 23536 2
CWMLITE 3 1537 14680064 3584 3
DRSYS 4 1985 12845056 3136 4
EXAMPLE 5 38881 196608 48 5
INDX 6 17 26148864 6384 6
TOOLS 7 1489 4390912 1072 7
USERS 8 17 26148864 6384 8

17 rows selected.

Mehul Shah

Well looking at the query output you posted. It seems that your temp file size is just 40MB. You can increase the temp file size to 200 MB and than try creating the index. Because when you are creating the index it needs space to sort the data and as you mentioned the table has 10 million rows it will definately need a bigger temp tablespace to create the index.

HTH

helpneed

hi

i think ui had given the query already ok ill post it once more for u

SELECT b.file_name, a.tablespace_name, a.bytes, a.blocks FROM dba_free_space a, dba_data_files b WHERE a.file_id = b.file_id AND a.tablespace_name = 'TEMP';

The above query will tell you how much contiguous free space is contained on each datafile.

regards

ASKER CERTIFIED SOLUTION

Nievergelt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hyynes

ASKER

Hi Nievergelt,

I use the statement you post lastly. and database altered. Then I issue create index statement and now index is also created.

Thanks.