10g features
- Renaming the table space
- Temporary table space group up to 9i only one temp. tables space
- 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 1st 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:
- Make the tablespace offline
- From the OS level physically copy the data file to the new destination.
- at SQL level rename the data file with a new location
- make the tablespace
- 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;