Question on adding tablespace on new mount points

Hi All,

Sorry that I already ask these questions before, but I was away for vacation and couldn't check the response after I come back. Hope you don't mind to provide your input again.

Server environment: Oracle 9.2.0.1.0 running on UNIX HP-UX11

Here are the questions:

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?
Does that mean no new tablespace are required as we are using AUTOEXTEND?

FYI...we are running out of disk space on the current mount points /bases

Filesystem          kbytes    used   avail %used Mounted on


/dev/vg00/lvol3     143360   31178  105194   23% /

/dev/vg00/lvol1      83733   36894   38465   49% /stand

/dev/vg00/lvol8    1142784  951689  180485   84% /var

/dev/vg00/lvol7    1216512  645059  535780   55% /usr

/dev/vg01/lv_users 3072000  600179 2318016   21% /users

/dev/vg00/lvol4      73728    1865   68074    3% /tmp

/dev/vg00/lvol6    4096000 3988152  101166   98% /opt

/dev/vg00/lvol5      53248    1117   48880    2% /home

2. If new tablespace should be created, should I use AUTOEXTEND? (see Create DB script at the end)
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?

(The tablespace usage and disk space used by tables are listed at the end)

4. What does that 'Extent' for each table used for?

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?

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

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 Jan 07 2004 - 02:39:26 CST