Extents explaination.. 1

[MCubitt]

I am learning day-by-day about Oracle .. a kind of "on the job training" with no tutor. Not an ideal situation but that's how it is.

We are using 9i but the supplier of our system who installed and configured Oracle, appear to have not used LMT so guess who has to MT!

Can I confirm that my understanding of extents is correct, please? If not, a simple no with comments on my misunderstanding will be fine, shooting me down in flames would be a little hurtful...!

1st... Segments relate to tables, indexes, packages.. any object contained inside a scheme?

2nd... Segments are created with an INITIAL size, NEXT EXTENT value and MAX ENTENT value.
The NEXT EXTENT value tells Oracle how much space to automatically add to the segment if it fills up.


So if segment EMPLOYEES was set at inital size 30MB and next extent of 8MB, when the segment becomes full it will grow to 38MB and the 'number of extents' will increase to 1.

If the 'number of extents' passes the 'max extents' an Oracle error message is issued.


So, to take my "real life" example, I have the following *problem*:

OWNER     SEGMENT_NAME                   TYPE       EXTENTS        BYTES NEXT_EXTENT MAX_EXTENTS
---------- ------------------------------ -------- --------- ------------ ----------- -----------
IFSAPP     PERIODICAL_TRANSACTION_PK      INDEX          231   30,408,704      106496        4096
IFSAPP     CUSTOMER_ORDER_LINE_HIST_TAB   TABLE          466   61,079,552      131072        4096
IFSAPP     CUSTOMER_ORDER_LINE_TAB        TABLE          317   41,549,824      131072        4096
IFSAPP     CUST_OPEN_ITEM_RPT             TABLE          742   97,255,424      131072        4096
IFSAPP     INVENTORY_TRANSACTION_HIST_TAB TABLE          513   67,239,936      131072        4096
IFSAPP     MANUF_STRUCTURE_JOURNAL_TAB    TABLE         2785  432,013,312    67108864        4096
IFSAPP     MANUF_STRUCTURE_TAB            TABLE          222   62,521,344    33554432        4096
IFSAPP     MPCCOM_ACCOUNTING_TAB          TABLE          706   92,536,832      131072        4096
IFSAPP     MRP_PART_SUPPLY_DEMAND_TAB     TABLE          261   34,209,792      131072        4096
IFSAPP     ORDER_INFO_SERVICES_RPT        TABLE          469   61,472,768      131072        4096
IFSAPP     PERIODICAL_TRANSACTION_TAB     TABLE          348   45,744,128      106496        4096
IFSAPP     REP_UPDATE_GENLED_RPT          TABLE          293   38,404,096      131072        4096

If my theory is right, segment MANUF_STRUCTURE_JOURNAL_TAB will add 67MB each extent. It seems a rather high value but who am I to argue?

To "reset" the problem, I have a number fo options, I believe...

Increasing the NEXT EXTENT would mean less extents would be needed because each one would do the job of many (if you see what I mean, 80MB would do the job of 10 8MB extents).

Increasing the MAX EXTENTS just prolongs the problem.

Ideally, resize with new storage parameters, e.g.
alter table TABLENAME move tablespace TABLESPACENAME storage (initial INITIAL_SIZE next NEXT_EXTENT);


Probably the worst ever explanation but am I even close? ;o)


Thanks.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Excellent questions, all.

1) Yes, You would want to run the scripts per tablespace, but since the script names are tablespace-specific, you can run the scripts in parallel for the various tablespaces. I run them in parallel to reduce the overall elapsed time to completion.

2) Rather than trying to consolidate an existing tablespace, I like to create a brand new LMT tablespace. It, too, can have 10 datafiles (or more, or less, if you chooses). I create all of my datafiles

...SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

This yields just-in-time storage allocation and most efficient and highest-performing extent allocation. And to confirm, a LMT can have 1 or many datafiles.

Then, once I have emptied out the old, fragmented tablespace(s), I drop the tablespace(s) and "rm" or "erase" the datafiles.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:51 (27Jul04) UTC (aka "GMT" and "Zulu"), 13:51 (27Jul04) Mountain Time)

 

[MCubitt]

Santa,

Thanks you for your answers and your patience.

Before I run this, can I confirm that my plan is sound? This is over a Development version of our DB but you know...

We currently have

TABLESPACE_NAME             FILE_ID FILE_NAME                                               BYTES
------------------------- --------- -------------------------------------------- ----------------
IFSAPP_ARCHIVE_DATA               8 /oracledata/IFSD/ifsapp_archive_data01.dbf        104,857,600
IFSAPP_ARCHIVE_INDEX              9 /oracleindex/IFSD/ifsapp_archive_index01.dbf      104,857,600
IFSAPP_DATA                       4 /oracledata/IFSD/ifsapp_data01.dbf              1,048,576,000
                                 10 /oracledata/IFSD/ifsapp_data02.dbf              1,048,576,000
                                 12 /oracledata/IFSD/ifsapp_data03.dbf              1,048,576,000
                                 15 /oracledata/IFSD/ifsapp_data04.dbf              1,048,576,000
                                 17 /oracledata/IFSD/ifsapp_data05.dbf              1,048,576,000
                                 19 /oracledata/IFSD/ifsapp_data06.dbf              1,048,576,000
                                 20 /oracledata/IFSD/ifsapp_data07.dbf              1,048,576,000
                                 21 /oracledata/IFSD/ifsapp_data08.dbf              1,048,576,000
                                 22 /oracledata/IFSD/ifsapp_data09.dbf              1,048,576,000
                                 23 /oracledata/IFSD/ifsapp_data10.dbf              1,048,576,000
                                 24 /oracledata/IFSD/ifsapp_data11.dbf              1,048,576,000
IFSAPP_INDEX                      5 /oracleindex/IFSD/ifsapp_index01.dbf            1,048,576,000
                                 11 /oracleindex/IFSD/ifsapp_index02.dbf            1,048,576,000
                                 13 /oracleindex/IFSD/ifsapp_index03.dbf            1,048,576,000
                                 16 /oracleindex/IFSD/ifsapp_index04.dbf            1,048,576,000
                                 18 /oracleindex/IFSD/ifsapp_index05.dbf            1,048,576,000
IFSAPP_REPORT_DATA                6 /oracledata/IFSD/ifsapp_report_data01.dbf       1,048,576,000
IFSAPP_REPORT_INDEX               7 /oracleindex/IFSD/ifsapp_report_index01.dbf     1,048,576,000
OEM_REPOSITORY                   14 /oracledata/IFSD/oem_repository01.dbf             104,857,600
SYSTEM                            1 /redolog1/IFSD/system01.dbf                     1,572,864,000
UNDOTBS                           2 /redolog2/IFSD/undotbs01.dbf                    1,363,148,800
USERS                             3 /oracleindex/IFSD/users01.dbf                      20,971,520
controlfile number :              1 /oracledata/IFSD/control01.ctl                      4,071,424
                                  2 /oracleindex/IFSD/control02.ctl                     4,071,424
                                  3 /oracledata/IFSD/control03.ctl                      4,071,424
online redo log group :           1 /redolog1/IFSD/redo01.log                          52,428,800
                                  1 /redolog2/IFSD/redo01a.log                         52,428,800
                                  2 /redolog1/IFSD/redo02.log                          52,428,800
                                  2 /redolog2/IFSD/redo02a.log                         52,428,800
                                  3 /redolog1/IFSD/redo03.log                          52,428,800
                                  3 /redolog2/IFSD/redo03a.log                         52,428,800
                                  4 /redolog1/IFSD/redo04.log                          52,428,800
                                  4 /redolog2/IFSD/redo04a.log                         52,428,800
                                  5 /redolog1/IFSD/redo05.log                          52,428,800
                                  5 /redolog2/IFSD/redo05a.log                         52,428,800
*************************                                                        ----------------
sum                                                                                22,682,427,392

So for my data I was planning this:

CREATE TABLESPACE IFSAPP_DATA DATAFILE '/oracledata/IFSD/ifsapp_dataLMT01.dbf' SIZE 2000M 
AUTOEXTEND ON NEXT 100M MAXSIZE 2000M 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

I was not too such how to find out which tablespaces have the LONGS and so I was preparing to process them ALL .. is that OK?

Thanks a million,

Martin




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

I don't see any flaws to the extent that I understand you plan, per se. There are, however, a couple of suggestions I might make.

Among my suggestions are:
Suggestion 1: Sizing your files. If you have an initial file "SIZE 2000M" and a "MAXSIZE 2000M", then the file does not, if fact, AUTOEXTEND since it is starting out at its maximum size. If I am creating a "new" tablespace to replace an "old" one, I total the current storage consumption of objects in the existing tablespace. Next, I "pad" that total amount with plenty of growth potential (for the sake of discussion let's "double" the actual consumption for growth space). I then divide that total by 2GB (my personal file maximum) to determine how many datafiles I want presently for a tablespace.

As an example, let's look at your "IFSAPP_DATA" tablespace. It has 11GB persently of file space. I'm guessing that your actual present consumption is far less than 11GB. For the sake of discussion, let's presume that your actual consumption is 5GB. So, using the above suggestion, I allow for growth by doubling 5GB (= 10GB), then divide by 2GB (my max file size) and arrive at five 2GB files. I would then create the 5 files (in a new LMT tablespace) at some reasonable initial file size with a reasonable NEXT parameter. Doing this causes the files to grow at an even, synchronized rate.

Suggestion 2: Determining current consumption. Although your posted listing of tablespace files is helpful, it is missing one very important component: Actual current consumption. Without that piece of information, we cannot accurately predict the number of (2GB-maximum) files we should allocate. Therefore, I'm posting my favourite script that discloses that information. First, I'll post the output from the script, and second, I post the script, itself:

Section 1 -- Tablespace-file Output Listing:

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ---------------------------------------------
PROV               4    2,097,152,000      555,745,280      521,732,096    1,575,419,904     75 Yes  'D:\ORACLE\ORADATA\TUMB817\TUMB817PROV01.DBF'
PROV               5    2,097,152,000      356,515,840      330,366,976    1,766,785,024     84 Yes  'D:\ORACLE\ORADATA\TUMB817\TUMB817PROV02.DBF'
RBS                2    2,097,152,000      178,257,920       42,606,592    2,054,545,408     97 Yes  'D:\ORACLE\ORADATA\TUMB817\TUMB817RBS01.DBF'
SYSTEM             1    2,097,152,000       58,720,256       55,697,408    2,041,454,592     97 Yes  'D:\ORACLE\ORADATA\TUMB817\TUMB817SYS01.DBF'
TEMP               3    2,097,152,000      188,743,680       40,796,160    2,056,355,840     98 Yes  'D:\ORACLE\ORADATA\TUMB817\TUMB817TEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000    1,337,982,976      991,199,232    9,494,560,768

Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
****************************************************************************************************************************************************

Section 2 -- Code for above "Tablespace-file Output Listing":

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

Please advise if you have addition questions or thoughts on this.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:06 (28Jul04) UTC (aka "GMT" and "Zulu"), 11:06 (28Jul04) Mountain Time)

 

[MCubitt]

Santa,

As always a helpful and friendly answer.

Thank you for the advice and, as requested, here are my results from running the script you kindly provided:

                                                                                         % Free                                                                      
                                                                                             of                                                                      
                                Total            Total                                     Pot.                                                                      
Tablespace                  Potential          Current                         Potential  Total Auto                                                                 
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename                                                        
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ------------------------------------------------------------    
IFSAPP_ARCHIVE_    8      104,857,600      104,857,600        1,056,768      103,800,832     98 No   '/oracledata/IFSD/ifsapp_archive_data01.dbf'                    
IFSAPP_ARCHIVE_    9      104,857,600      104,857,600        1,056,768      103,800,832     98 No   '/oracleindex/IFSD/ifsapp_archive_index01.dbf'                  
IFSAPP_DATA        4    1,048,576,000    1,048,576,000    1,048,453,120          122,880      0 No   '/oracledata/IFSD/ifsapp_data01.dbf'                            
IFSAPP_DATA       10    1,048,576,000    1,048,576,000    1,048,453,120          122,880      0 No   '/oracledata/IFSD/ifsapp_data02.dbf'                            
IFSAPP_DATA       12    1,048,576,000    1,048,576,000    1,048,453,120          122,880      0 No   '/oracledata/IFSD/ifsapp_data03.dbf'                            
IFSAPP_DATA       15    1,048,576,000    1,048,576,000      738,992,128      309,583,872     29 No   '/oracledata/IFSD/ifsapp_data04.dbf'                            
IFSAPP_DATA       17    1,048,576,000    1,048,576,000      331,751,424      716,824,576     68 No   '/oracledata/IFSD/ifsapp_data05.dbf'                            
IFSAPP_DATA       19    1,048,576,000    1,048,576,000      150,478,848      898,097,152     85 No   '/oracledata/IFSD/ifsapp_data06.dbf'                            
IFSAPP_DATA       20    1,048,576,000    1,048,576,000      128,589,824      919,986,176     87 No   '/oracledata/IFSD/ifsapp_data07.dbf'                            
IFSAPP_DATA       21    1,048,576,000    1,048,576,000      201,334,784      847,241,216     80 No   '/oracledata/IFSD/ifsapp_data08.dbf'                            
IFSAPP_DATA       22    1,048,576,000    1,048,576,000      284,696,576      763,879,424     72 No   '/oracledata/IFSD/ifsapp_data09.dbf'                            
IFSAPP_DATA       23    1,048,576,000    1,048,576,000      492,576,768      555,999,232     53 No   '/oracledata/IFSD/ifsapp_data10.dbf'                            
IFSAPP_DATA       24    1,048,576,000    1,048,576,000      441,196,544      607,379,456     57 No   '/oracledata/IFSD/ifsapp_data11.dbf'                            
IFSAPP_INDEX       5    1,048,576,000    1,048,576,000      350,625,792      697,950,208     66 No   '/oracleindex/IFSD/ifsapp_index01.dbf'                          
IFSAPP_INDEX      11    1,048,576,000    1,048,576,000      941,367,296      107,208,704     10 No   '/oracleindex/IFSD/ifsapp_index02.dbf'                          
IFSAPP_INDEX      13    1,048,576,000    1,048,576,000      654,843,904      393,732,096     37 No   '/oracleindex/IFSD/ifsapp_index03.dbf'                          
IFSAPP_INDEX      16    1,048,576,000    1,048,576,000      346,955,776      701,620,224     66 No   '/oracleindex/IFSD/ifsapp_index04.dbf'                          
IFSAPP_INDEX      18    1,048,576,000    1,048,576,000      285,351,936      763,224,064     72 No   '/oracleindex/IFSD/ifsapp_index05.dbf'                          
IFSAPP_REPORT_D    6    1,048,576,000    1,048,576,000      391,127,040      657,448,960     62 No   '/oracledata/IFSD/ifsapp_report_data01.dbf'                     
IFSAPP_REPORT_I    7    1,048,576,000    1,048,576,000        9,576,448    1,038,999,552     99 No   '/oracleindex/IFSD/ifsapp_report_index01.dbf'                   
OEM_REPOSITORY    14    1,048,576,000      104,857,600       91,750,400      956,825,600     91 Yes  '/oracledata/IFSD/oem_repository01.dbf'                         
SYSTEM             1   34,359,721,984    1,572,864,000      971,554,816   33,388,167,168     97 Yes  '/redolog1/IFSD/system01.dbf'                                   
UNDOTBS            2   34,359,721,984    1,363,148,800       28,639,232   34,331,082,752     99 Yes  '/redolog2/IFSD/undotbs01.dbf'                                  
USERS              3       20,971,520       20,971,520            8,192       20,963,328     99 No   '/oracleindex/IFSD/users01.dbf'                                 
                     ---------------- ---------------- ---------------- ----------------                                                                             
sum                    88,873,074,688   22,145,925,120    9,988,890,624   78,884,184,064                                                                             

24 rows selected.

I had thought (incorrectly) that the max size was per datafile and so it would generate subsequent datafiles right away.


Thanks,

Martin


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

Thanks for posting your results. It presents a very clear picture of your disk-consumption situation.

A few comments/assessments:

1) You are correct about "...the max size was per datafile..."; new files, however, do not result automatically from the filling-to-the-max of the existing datafiles.

2) Notice that the 210MB tablespace, IFSAPP_ARCHIVE_..., actually occupies only 2MB of space. Unless you expect significant growth within this tablespace, over 200MB of space will lie dormant indefinitely. This is a great example of how AUTOEXTEND-ing a file from a small initial size, in reasonable increments, can make use of space on an "as-needed, just-in-time" basis. Under such circumstances, the 200MB+ of space remains available for immediate use by any application (including IFSAPP_ARCHIVE_... tablespace).

3) Notice that only three of your 24 files are AUTOEXTEND-ing. Two of the AUTOEXTEND-ing files have an "unlimited" (34GB) of MAXSIZE. I recommend no more than "MAXSIZE 2000M" (2GB) for any file since a) some operating systems still have trouble with files exceeding 2GB and b) several 2GB-maximum files are easier to manage than one, large 34GB file.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:23 (29Jul04) UTC (aka "GMT" and "Zulu"), 02:23 (29Jul04) Mountain Time)

 

[mrn]

Thanks Santa,

A true Star.......

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[MCubitt]

Santa,

Thanks again. Final question(s!) for now..

new files, however, do not result automatically

So forgive my misunderstanding, but we must manually create these still (as we do with non-LMT)? The difference is the datafiles do not start at their maximum.

In our case, I would need to set a number of 2Gb datafiles for IFSAPP_DATA wouldn't I? How would I do that with the create tablespace command, splitting each with a comma?

CREATE TABLESPACE IFSAPP_DATA DATAFILE '/oracledata/IFSD/ifsapp_dataLMT01.dbf' SIZE 2000M,
DATAFILE '/oracledata/IFSD/ifsapp_dataLMT02.dbf' SIZE 2000M,
DATAFILE '/oracledata/IFSD/ifsapp_dataLMT03.dbf' SIZE 2000M,
DATAFILE '/oracledata/IFSD/ifsapp_dataLMT04.dbf' SIZE 2000M,
DATAFILE '/oracledata/IFSD/ifsapp_dataLMT05.dbf' SIZE 2000M,
AUTOEXTEND ON NEXT 100M MAXSIZE 100000M 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

Here are some comfirmations and corrections to your issues:

...we must manually create these [files] still (as we do with non-LMT)?

Yes, all files in an Oracle database result from your explicit creation of those files. There are no implicit/automatic file creations in Oracle (regardless of whether they support a LMT or a non-LMT tablespace). Extensions to those files, however, can occur automatically (to their stated MAXSIZE parameter) when the files are in AUTOEXTEND mode.

The difference is the datafiles do not start at their maximum.

Regardless of whether a file is LMT or non-LMT, files start at their MAXSIZE only when "SIZE" equals "MAXSIZE". There is nothing about Oracle database datafiles that implicitly causes their initial size to equal their maximum unless the file is "AUTOEXTEND OFF", which is the default.

In our case, I would need to set a number of 2Gb datafiles for IFSAPP_DATA wouldn't I?

No, I would never create files initially to their maxsize of 2GB since I believe in "just-in-time" allocation of storage. I start the files at relatively small initial sizes (e.g., 10M) with relatively small increments (NEXT 10M), so that the various files grow in an even, nearly synchronized fashion.

How would I do that with the create tablespace command...?

Here is your code from above as I would code it (and as it needs to appear syntactically for Oracle):

CREATE TABLESPACE IFSAPP_DATA DATAFILE '/oracledata/IFSD/ifsapp_dataLMT01.dbf' SIZE 10M
            AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
         '/oracledata/IFSD/ifsapp_dataLMT02.dbf' SIZE 10M
            AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
         '/oracledata/IFSD/ifsapp_dataLMT03.dbf' SIZE 10M
            AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
         '/oracledata/IFSD/ifsapp_dataLMT04.dbf' SIZE 10M
            AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
         '/oracledata/IFSD/ifsapp_dataLMT05.dbf' SIZE 10M
            AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
         EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Note: "EXTENT MANAGEMENT LOCAL AUTOALLOCATE" is what creates a Locally Managed Tablespace (LMT). Local management of extents means simply that when a table or index needs more space, Oracle manages that NEXT allocation of space according to its own internal algorithm and stores the information about the extent(s) in a bit-mapped fashion within the object itself, rather than storing the extent information in the data dictionary as Oracle did formerly with non-LMT tablespaces. The new LMT scheme is much faster and relieves DBAs from worrying about specifying, for tables and indexes, such parameters and INITIAL, NEXT, PCTINCREASE, and MAXEXTENTS -- LMTs take care of all of that.

I hope this helps. If more questions or clarifications are in order, we welcome them.

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:34 (29Jul04) UTC (aka "GMT" and "Zulu"), 03:34 (29Jul04) Mountain Time)

 

[MCubitt]

Great stuff! Now can you tell me how to find out which tablespaces have the "LONGS" please?

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[mrn]

Also, If we had LOB's how would we move them?

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[SantaMufasa]

Martin,

The following script (which I call, "LongFinder.sql") lists the tablespace name, table, and datatype that will not MOVE unless you use "exp"/"imp":

col a heading "Table Name containing LOB" format a45
col b heading "Specific|LOB|Column|Type" format a8
col tablespace_name format a30
set pagesize 5000
set linesize 90
select tablespace_name,c.owner||'.'||table_name a, data_type b
from dba_tab_columns c, dba_segments s
where (data_type like '%LOB%'
or data_type like '%LONG%')
and length(data_type)<= 8
and tablespace_name <> 'SYSTEM'
and c.owner = s.owner
and segment_name = table_name
order by tablespace_name,c.owner,table_name
/

Here is sample output from running the above script:

                                                                             Specific
                                                                             LOB
                                                                             Column
TABLESPACE_NAME                Table Name containing LOB                     Type
------------------------------ --------------------------------------------- --------
DATA1                          DHUNT.HL7_STORE_MESSAGE_TEXT                  LONG
DATA1                          DHUNT.HL7_STORE_MESSAGE_TEXT_BACKUP           LONG
DATA1                          DHUNT.S_IMAGE                                 LONG RAW
DATA1                          DHUNT.S_WAREHOUSE                             LONG
DATA1                          SUMMIT.S_IMAGE                                LONG RAW
DATA1                          SUMMIT.S_WAREHOUSE                            LONG
DATA1                          TEST.CLOBBER                                  CLOB
DATA1                          TEST.ELTON                                    BLOB
DATA1                          TEST.LONG1                                    LONG
DATA1                          TEST.LONGRAWTABLE                             LONG RAW
DATA1                          TEST.OLEMMA                                   CLOB
DATA1                          TEST.P_HOLDING                                LONG
DATA1                          TEST.S_IMAGE                                  LONG RAW
DATA1                          TEST.S_WAREHOUSE                              LONG
DATA1                          TEST.TEMP                                     LONG
DATA1                          TEST.TEMP_LONG                                LONG
DATA1                          TEST.TEST_CLOB                                CLOB
DATA1                          TEST.YADA3                                    CLOB
DATA1                          YADA.S_IMAGE                                  LONG RAW
DATA1                          YADA.S_WAREHOUSE                              LONG

20 rows selected.

Mike,

As we mentioned earlier, to relocate the objects appearing in the output of the above script, you would use the script appearing in Item 3, above (entitled, "If you have any tables with LONG columns, they will not "MOVE"; you must export them, then import them") from my earlier post timestamped "15:54 (27Jul04) UTC (aka "GMT" and "Zulu"), 08:54 (27Jul04) Mountain Time".

Let me know if you have additional questions/issues.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:11 (29Jul04) UTC (aka "GMT" and "Zulu"), 10:11 (29Jul04) Mountain Time)

 

[mrn]

Right I moved all the long tables using the imp/exp script and I droped the tables afterwards, but when I try to drop the tablespace I get the following

SQL> drop tablespace tools;
drop tablespace tools
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

so I try with

SQL> drop tablespace tools INCLUDING CONTENTS;
drop tablespace tools INCLUDING CONTENTS
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

I am now very confused....

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[mrn]

I have now moved the partition to the new tablespace but still can't move the table itself, ideas?

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.