pct free

[reydp]

Originally posted by julian

Just to remind:
Increasing your db blocksize means you have to recreate your db. :-))

Yes, below 9i.

For a 9i running instance, you can create a new tablespace with, say, 16K block size:

create tablespace tbs16K
datafile '/ora900/oradata/tbs16K.dbf' size 256M
blocksize 16k;

Well nice to hear from you Julian about that realistic feature of 9i, how about that one they called a self-DBAdministered system, is this true also? they say all you need is to identify your system if it is an OLTP,
DW/Datamart or HYBRID, and 9i system will do the DBA works for the system.

Anyway, thus helpme's db is 9i?

[helpme]

no :(

[reydp]

what is your AVG_ROW_LEN
db_block_size?

[julian]

Well nice to hear from you Julian about that realistic feature of 9i, how about that one they called a self-DBAdministered system, is this true also? they say all you need is to identify your system if it is an OLTP,
DW/Datamart or HYBRID, and 9i system will do the DBA works for the system.
Anyway, thus helpme's db is 9i?

Yes, in 9i, you can use OMFs (Oracle Managed Files):

In init.ora set DB_CREATE_FILE_DEST=/oradata/9i/disk7/

Then create the tablespace using the OMF syntax:

create tablespace OMT datafile size 128M;

Then add a datafile to the tablespace via the OMF syntax:

alter tablespace OMT add datafile '=/oradata/9i/disk7/file_4.dbf';

It is not a good idea to name the fiels by yourself, not recommeneded at all.

If you drop the tablespace then the good thing is that all datafiles are removed from the operating system, good isn't it! Up top now, we had to do this manually.

If you have a tablespace ala_bala with both OMF and non-OMF datafiles then you can drop it with:

drop tablespace ala_bala including contents and datafiles;

And so on, and so on...

During the ILT course "Oracle 9i: New features for administraotors" I was told that you should not to use OMFs if you know how to manage non-OMFs.

These new features are meant for DBSs, not for DBAs :-)










[Edited by julian on 11-23-2001 at 06:40 AM]

[helpme]

the avg row len for that table is 4330
and the db block size is 2048
and i suppose i cannot change this..rite cuz mine is 8x :(
Thanx
Sam

[reydp]

This is another INPUT Julian.... thanx alot :-))
I haven't handled 9i yet, I told my boss to wait until next year.

[julian]

Originally posted by reydp
This is another INPUT Julian.... thanx alot :-))
I haven't handled 9i yet, I told my boss to wait until next year.

Good idea! Around March Oracle will realease 9.0.2. Probably then is the right time to upgrade production (if needed).

[reydp]

with 2048 as db_block size, that's small !!!
If you can afford to recreate your db, then that is your best option.
and make you db block size to 16k

If not, one option is to modify in the design level:
make that 1 table into 2 table(split)
let's say original table is:
f1 number(4)
f2 varchar2(4000)
f3 varchar2(4000)
f4 varchar2(4000)
f5 varchar2(4000)

and f1 is your primary then:
table 1:
f1 number(4)
f2 varchar2(4000)
f3 varchar2(4000)

table 2:
f1 number(4)
f4 varchar2(4000)
f5 varchar2(4000)

[reydp]

Sorry for the 2nd option, even splitting the table still there will be a problem. since db block size is not even half of the average row length.

[helpme]

I hope this thing would nor really affect affect my db performance..cuz there are quite a lot of select on this table...
Thanx