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 tablespace ts_dict;

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

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

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

To delete tablespace along with data files which it contains

SYS> drop tablespace ts_dict including contents and data files;  

Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

To make the tablespace contents in read-only mode

SYS> alter tablespace ts1 read-only;

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

To make the tablespace contents in read & write mode

SYS> alter tablespace ts1 read write;  

To make a tablespace offline

SYS> alter tablespace ts1 offline:  

To make tablespace  online

SYS> alter tablespace 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 a temporary tablespace group [ 10g feature]

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

SYS> select * from dba_table space_groups;  

To add a tablespace togroup1

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

To assign group1 to default temporary tablespace

[initially, it was “temp”]

SYS> alter database default temporary tablespace group1;

SYS> select * from dba_properties;  

To create big file table space

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

SYS> select * from dba_table spaces;

[observe the big file column]

Resizing label space

SYS> alter tablespace ts_big resize 25m

Tp rename ][or] move a data file  

Steps:

1. Make the tablespace offline
2. From the OS level physically copy the data file to the new destination.
3. at SQL level rename the data file with a new location
4. make the tablespace
5. 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;    

Oracle DBA Interview Questions