Bigfile Tablespace in 10g

Database tables nowdays holds alot of data. This is of because data is important for companies to make decisions.As you are aware the concept of BI (Business Intelligence), it is becoming famous nowdays. BI needs alot of data to make better decisions.

To address this craving,Oracle has come up with th Bigfile Tablespace concept.

Bigfile tablespaces(BFT) are tablespaces with a single large datafile(this means you can only have 1 datafile for bigfile tablespace). In contrast to normal (smallfile) tablespaces can have several datafiles(max 1022 files), but each is limited in size (the size of the disk partiton).

This concept is introduced in 10g.

Bigfile tablespaces(BFT) must be locally managed with automatic segment-space management.

Temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces are exceptions to this rule.

e.g. of creating a bigfile tablespace

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G;

OR

CREATE BIGFILE TABLESPACE innotiivebig DATAFILE '/a1/oracle/innotiivebig01.dbf' SIZE 200G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The size of the file can be specified in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

DBA_TABLESPACES AND V$TABLESPACE contain a new column called BIGFILE that allows administrators to determine if the tablespace is a Smallfile or Bigfile tablespace.

Before you even consider creating one of these things, make sure that your environment is able to support its expansion

For using BFT,the underlying operating system should support Large File.In other words the file system should have Large File Support(LFS).

Bigfile tablespaces will have a future in many Oracle environments but they must only be implemented with much forethought.

Oracle Recycle Bin

This is a new feature in Oracle10g.Prior to 10g,when you issue the DROP command
e.g. DROP table EMP;

The particular table is dropped and also its dependent objects (indexes,costraints and triggers,etc).
In Oracle 10g it will be renamed to a system generated string and logically placed in the recycle bin.Each dropped object will be placed in the recycle bin which is in the same tablespace the schema from which it was dropped from.

The recycle bin is actually a new data dictionary table(SYS.RECYCLEBIN$) that records information about dropped tables.When an object is dropped,Oracle just renames the table and all its associated objects (indexes, triggers,etc) to a system-generated name that begins with BIN$.
There are two recyclebin views, USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

Users will retain all writes and privileges to the recyclebin object just as before the object was dropped.
There is no special storage for these dropped objects because it will still remain in the tablespace they were dropped from.
Dropped objects is still counted in the users's quota.

Here are some examples.Have fun!!!

SQL> create table t (name varchar2(8));
Table created.

SQL> insert into t values ('ali');
1 row created.

SQL> insert into t values ('abu');
1 row created.

SQL> select * from t;
NAME
--------
ali
abu

SQL> drop table t;
Table dropped.

SQL> select * from t;select * from t
*ERROR at line 1:ORA-00942: table or view does not exist

SQL> select object_name,original_name,ts_name,droptime from recyclebin;

OBJECT_ NAME ORIGINAL_NAME TS_NAME DROPTIME
---------------------- ------------------- ------------ ---------------
BIN$DgYaFTOBTrScJ+2ba85IXA==$0 T USERS 2007-03-15:10:14:22

You can also type

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME-------------- --------------- --------------------- --------------- -------------
T BIN$DgYaFTOBTrScJ+2ba85IXA==$0 TABLE 2007-03-15:10:14:22


To query the content of a table in recyclebin

SQL> select * from "BIN$DgYaFTOBTrScJ+2ba85IXA==$0";
NAME
--------
ali
abu

to recover the dropped table
SQL> flashback table t to before drop;
Flashback complete.

SQL> select * from t;
NAME
--------
ali
abu

SQL> select * from recyclebin;
no rows selected

Some other commands
1.to recover the table to a different name
SQL> flashback table t to before drop rename to t1;

2. To drop permanently(do not go to recyclebin)
SQL> DROP table t purge;

When you issue the DROP TABLESPACE....INCLUDING CONTENTS,all objects will be drop and won't go to the recyclebin and all objects in the recyclebin will be removed.

Usually indexes,constraints and triggers should retain the original name after the dropped table is recovered but now the indexes has system generated names not original names.This is a bug in Oracle.

SPFILE or PFILE

Someone ask me the other day the differences between spfile and pfile

Here are my input:

PFILE or SPFILE are file that is used to store database intialization parameters.

The pfile or spfile is read during instance startup to get instance specific settings.

SPFILE was implemented or introduced from 9i only.In 10g pfile is still supported
You only can use one at a time but both files can exist in the default location.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use the spfile.

Changes to pfile can be made by editing the file and do a shutdown startup but for spfile, changes are made using the alter system command.

To create a server parameter file from a parameter file
login as sys
create spfile from pfile;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

By using spfile changes can be made in 3 ways as specified below
SCOPE = SPFILE
e.g. alter system set sql_trace=true scope =spfile;
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)

SCOPE = MEMORY
e.g alter system set undo_tablespace=undotbs1 scope=memory;
(For dynamic parameters, changes are applied in memory only. No static parameter changes is allowed.)

SCOPE = BOTH
e.g alter system set log_archive_dest_1 =/u01/arch2 scope=both;
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameters changes is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

We can also use the server parameter file to create a pfile
This would be useful for:
1) Creating backups of the spfile.
2) For diagnostic purposes
3) Modifying the spfile by first exporting it, editing the output file, and then recreating it.

The following example creates a pfile from spfile
login as sys
CREATE PFILE FROM SPFILE;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

Fell free to comment or ask questions....Thanks

Welcome all Malaysian Oracle Users

I am starting this blog to help Malaysian Oracle Users, so that they can share their experiences on Oracle Databases (7,8,8i,9i,10g)

I will try my best to post new/fresh/old experiences that I have encountered as a Database consultant.

Please give your comments if there is any mistakes or how to make this blog better.

Hope to do it weekly. WATCH OUT!!!