Introduction to Oracle 10g DBA

10g features

1. Renaming the table space
2. Temporary table space group up to 9i only one temp. tables space
3. Big file table space up to 120 tb., at table space level

SYS> desc dba _tabespaces

SYS> select table space_name, block_size, status, extent_managment, contents from dbna_table spaces;

SYS> sava dba_ts

SYS> desc dba_data_files

SYS> desc v $tempfile

SYS> desc data base_properties to create table space ls1

SYS> create table space ts1 datafile ‘/disk4/oradata/pal/ts1.dbf’ size 10m;

[default locally managed [in 10g]

SYS> @dba_ts

SYS> create table space ts_dict data file ‘disk4/oradata / pal/ts_dict.dbf’ size 10m extent management  dictionary;

[to create table space is_dict]

SYS> @dba_ts

to increasing the data file size of existing table space

SYS> alter table space 1^st add datafile ‘/disk4/oradata/pal/ts11.dbf’size 5m;

SYS> select * from dba_data_files;

to resize exioting data file

SYS> alter database datafile ‘/disk4 / oradata/pal/ts11.dbf’ resize 10m;

To drop label space

SYS>drop table space ts_dict;

only the logical path will be broken but @ os level the data files which are under this table space will be existing physically

To establish the logical path between physical files and newly creating table space.

SYS> create table space ts_dict data file ‘/disk4/oradata/pa;/ts_dick.dbf’reuse;

To delete table space along with data fils which it contain

SYS> drop table space ts_dict including contents and data files;

To make the table space contents in read only mode

SYS> alter table space ts1 read only;

At read only mode we drop the table space but can’t perform only DML operations can only select (DRL)

To make the table space contents in read & write mode

SYS> alter table space ts1 read write;

To make table space offline

SYS> alter table space ts1 offline:

To make table space  online

SYS> alter table space ts1 online;

can perform DML, DBL, Txs.

In 10s the default block size is 8k

to make the block size as 2k or 4k………

[dba12 dbs] $ vi initrajpar. Ora

Db_2K_cache_size = 16m

:wq

SYS> start up force

SYS> sho parameter db-2K

To create data file size as 2k

SYS> create table space ts-2k data file ‘/disk4/oradata/pal/ts_2k.dbf ‘size 10m blocksize 2k;

SYS> @ dba_ts

Renaming table space

SYS> alter table space ts1 rename to ts2;

To create nologging table space

SYS> create table space ts-log data file ‘/disk4/oradata/pal/ts-log.dbf’ size 5m nologging;

SYS> select* from dba_table spaces:

Observe the logging column.

To create temporary table space group [ 10g feature]

SYS> create temporary table space ttsg1 tempfile ‘disk4/oradata/pa;/ttsg1/dbf’ size 10m table space group group1;

SYS> select * from dba_table space_groups;

To add a table space togroup1

SYS> create temporary table space ttog2 tempfile ‘/disk4/oradata/pal/ttsg2.dbf’ size 15m table space group group1;

To assign group1 to default temporary table space

[initially it was “temp”]

SYS> alter database default temporary table space group1;

SYS> select * from dba_properties;

To create big file table space

SYS> create big file table space ts_big data file ‘/disk4/oradata/pal/ts_big.dbf’ size 20m;

SYS> select * from dba_table spaces;

[observe the bigfile column]

Resizing label space

SYS> alter table space ts_big resize 25m

Tp rename ][or] move a data file

Steps:

1. Make the table space offline
2. From OS level physically copy the data file to new destination.
3. at SQL level rename the data file with new location
4. make the table space online.

SYS> create table space temp1 data file ‘/disk4/oradata/pal/ temp1/ dbf’ size.5m;

SYS> alter table space temp1 offline;

SYS> !

[dba12 ~] $ cd /disk4/oradata/pa;

[dba12 pa;] $ cp temp1.dbf /disk 3/oradata/pal /temp1.dbf

[dba12 pal]$ exit

SYS> alter table space temp1 rename data file ‘/disk4 /oradata / pal/ temp1.dfb’;

SYS> qlter  table space temp1 oline;

SYS> select * from dba _ data_ files;