Question on adding or moving tablespace on new hard disks

From: Ursula Lee <ursula.lee_at_thales-is.com>
Date: Wed, 17 Dec 2003 16:49:58 +0800
Message-ID: <brp59o$dba19@imsp212.netvigator.com>

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?
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

/dev/vg01/lv_extopt 786432 484905 282713 63% /extopt
/dev/vg01/lv_bases 5017600 5010742 6536 100% /bases

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 Dec 17 2003 - 02:49:58 CST