Ooops...I am using Oracle 9.2.0.1.0 running on UNIX HP-UX11.
Appreciate all your input.
Ed Stevens wrote:
> 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 - 20:06:37 CST
