Re: Question on adding or moving tablespace on new hard disks

Ursula,

I don't see where you've ever stated which version of Oracle or which OS, though it's easy to surmise some form of Unix. This is always critical information when asking any question about Oracle.

Further comments embedded.

On Wed, 17 Dec 2003 16:49:58 +0800, Ursula Lee <ursula.lee_at_thales-is.com> wrote:

>Hi All,
>
>I am going to create the new mount points /bases2 and /bases3 on the 2
>new hard disk (2 external hard disks 18G each, so 36G in total).
>(see create tablespace script at the end)
>(See the tablespace distribution and the table size usage at the end)
>
>Question:
>1. For all of the existing tablespace, we are using autoextend, so does
>that mean we can continue to use our existing tablespace name on the new
>mount points as I am planning to alter the existing one to the new mount
>points?

As pointed out in reply to your earlier posting on this, you can simply add additional files to the tablespace. A tablespace is made up of one or more files, and the multiple files don't have to be on the same mount point.

>Does that mean no new tablespace are required as we are using AUTOEXTEND?
>

AUTOEXTEND, in and of itself, is not a factor in this. I'd suggest you read the docs on what AUTOEXTEND actually does.

>FYI...we are running out of disk space on the current mount points /bases
<snip>
>2. If new tablespace should be created, should I use AUTOEXTEND? (see
>Create DB script at the end)

Again, read what AUTOEXTEND does. Decide if that's the behavior you want. Some people think it's the greatest thing since sliced bread, other's think it's the greatest evil in database management. You'll have to decide for yourself once you understand what it actually does.

>3. I have 2 big tables HISEVENT, HISVALUE, 11 summary tables and 2
>mapping tables. Currently, I distribute all these tables in different
>tablespace as the some of them have almost 100% usage.
>Is this the proper way to separate the tables into different tablespace
>or should I create a big one to store all the tables?
>

There really is no "proper" way. It all depends.
>(The tablespace usage and disk space used by tables are listed at the end)
>
>4. What does that 'Extent' for each table used for?
An extent is logically contiguous chunk of a tablespace that is staked out for use by a given object, such as a table. Once an extent is filled, another will be acquired, subject to various rules. If you are asking what the word 'extent' means within the CREATE TABLE or CREATE TABLESPACE syntax, that's just the first word of the EXTENT MANAGEMENT clause. The choices are EXTENT MANAGEMENT LOCAL or EXTENT MANAGEMENT DICTIONARY. The use of LOCAL is a good thing, but I'd suggest you follow it up with a uniform extent size. Again, check the docs for details. BTW, defining the extent size at the tablespace does not really create any extents -- it simply defines an enforced default to be used when objects are created within the tablespace.

>
>5. To create the new mount points on UNIX, I also have to specify how
>big for the storage, normally, how do you estimate and inform the UNIX
>Admin? I believe we can utilize all of the 18 G on each hard disk, i.e.
>36 G for just datafile storage, right?
>

Given an existing system that needs expansion, I estimate based on current usage. Increase by some factor that seems appropriate. Maybe 25%, maybe 50%, maybe . .. who knows? This is for you to decide.

How do I inform the Unix admin? Usually by a telephone call or e-mail.

>Appreciate your help as I am just an Oracle Developer without any DBA
>background.

You stated this in your first posting (Dec 9). If you are having to do the tasks implied by your questions, then you are the DBA, like it or not, trained or not. If this is not the case, -- if there really is a DBA -- then he should be the one dealing with this, not a developer. Assuming you really are having to act as the DBA, go now to tahiti.oracle.com and start getting familiar with what's there. It will require a registration, but it is free and signing up will be the best investement of 30 seconds in your career you will ever do. I'd start with the concepts manual in the documentation section.

>
>P.S.
>1. The following illustrates the script to create tablespace
>connect SYS/change_on_install as SYSDBA
>set echo on
>spool /opt/oracle/product/oracle9i/admin/TKEDBMS/create/CreateDBFiles.log
>CREATE TABLESPACE "INDX" LOGGING DATAFILE
>'/bases/data/oradata/TKEDBMS//bdoracle/indx1_tkedbms.dbf' SIZE 25M REUSE
>AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED,
>'/opt/oracle/product/oracle9i/oradata/TKEDBMS/.dbf' SIZE 5M EXTENT
>MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
>CREATE TABLESPACE "RBS" LOGGING DATAFILE
>'/bases/data/oradata/TKEDBMS//bdoracle/ts_tkedbms_rbs1.dbf' SIZE 100M
>REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
>CREATE TABLESPACE "TOOLS" LOGGING DATAFILE
>'/bases/data/oradata/TKEDBMS//bdoracle/ts_tkedbms_tools.dbf' SIZE 10M
>REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
>SEGMENT SPACE MANAGEMENT AUTO ;
>CREATE TABLESPACE "USERS" LOGGING DATAFILE
>'/bases/data/oradata/TKEDBMS//bdoracle/ts_tkedbms.dbf' SIZE 25M REUSE
>AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
>SEGMENT SPACE MANAGEMENT AUTO ;
>CREATE TABLESPACE "XDB" LOGGING DATAFILE
>'/bases/data/oradata/TKEDBMS//bdoracle/ts_tkedbms_xdb1.dbf' SIZE 20M
>REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
>SEGMENT SPACE MANAGEMENT AUTO ;
>spool off
>exit;
>
>2. The following shows the tablespace usage and table size
>Name Type Extent Management Size (M) Free (MB) Free %
>INDX PERMANENT LOCAL 30 29.875 99.58
>RBS PERMANENT LOCAL 100 94.063 94.06
>SYSTEM PERMANENT LOCAL 290 10.438 3.6
>TOOLS PERMANENT LOCAL 10 9.938 99.38
>UNDOTBS1 PERMANENT LOCAL 1190 13.688 1.15
>USERS PERMANENT LOCAL 1425 895.375 62.83
>XDB PERMANENT LOCAL 38.125 0.188 0.49
>TEMP PERMANENT LOCAL 104 1 0.96
>
>
>Table Tablespace Total Space Used (Bytes) Total Extents
>AFC RBS 720896 11
>CURR RBS 3145728 18
>ECS_SYS USERS 3145728 18
>POW_SYS URBS 65536 1
>VOLTAGE RBS 2097152 17
>HISEVENT USERS 31457280 45
>HISVALUE USERS 134217728 87
>LOCATION USERS 65536 1
>REST_SYS RBS 65536 1
>COMMS_SYS RBS 65536 1
>TEMP_SENSOR USERS 41943040 55
>USER_ACCESS USERS 65536 1
>EQUIP_ENERGY USERS 655360 10
>HUMID_SENSOR USERS 4194304 19
>EQUIPMENT_LABEL USERS 131072 2
>
>
>
>Regards,
>Ursula
>
>
Received on Wed Dec 17 2003 - 08:29:51 CST