[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20456
  • Last Modified:

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.
0
hyynes
Asked:
hyynes
  • 4
  • 3
  • 3
  • +2
1 Solution
 
penzk001Commented:
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.


0
 
penzk001Commented:
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..
0
 
NievergeltCommented:
I would suggest to use

SELECT * FROM DBA_TABLESPACES;

to list all tablespaces.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hyynesAuthor Commented:
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 ....
0
 
NievergeltCommented:
Please post what you get from
SELECT * FROM DBA_TEMP_FILES;
0
 
penzk001Commented:
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.
0
 
helpneedCommented:
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








0
 
Mehul ShahIT consultantCommented:
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
0
 
hyynesAuthor Commented:
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 ????
0
 
helpneedCommented:
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


0
 
hyynesAuthor Commented:
>>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.
0
 
Mehul ShahIT consultantCommented:
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
0
 
helpneedCommented:
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


0
 
NievergeltCommented:
Hi hyynes

Sorry I was slow to respond.

Your query result shows that your TEMP tablespace has one file (D:\ORACLE\ORA9I\ORADATA\ORCL\TEMP01.DBF) and that autoextending the file is turned off (DBA_TEMP_FILES.AUTOEXTENSIBLE is NO).
If you turn autoextend on, the database engine will resize the datafile automatically as required:

ALTER DATABASE TEMPFILE 'D:\ORACLE\ORA9I\ORADATA\ORCL\TEMP01.DBF'
    AUTOEXTEND ON NEXT 10M

The amount behind next is the increase of the file, when required. You can change that to a value that better suits you; I just set it to 10M blocks. With a blocksize of 8KB this amounts to 80MB increase.

Hope this helps   Christoph
0
 
hyynesAuthor Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now