狠狠撸

Sql>select tablespace_name, file_name from dba_data_files union select tablespace_name, file_name
from dba_temp_files order by 1;

To reduce character of the file_name,define_editor=vi, name permanently and sqlname:

Sql>!

$cd $ORACLE_HOME/sqlplus/admin/

$vi glogin.sql

G (to go to last line)

O (it gives u new line to write)

define_editor=vi

col file_name format a35

col name format a30

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"

Tablespace Management Practical’s:--

Views:--

V$sga, v$sgastat, v$sgainfo

V$sysaux_occupants

Desc dba_tablespaces;

Desc dba_data_files;

Desc v$tablespace;

Desc v$datafile;

Desc v$database;

From 8i onwards we can maintain tablespaces locally.

1. To create locally managed tablespace:--

create tablespace tname datafile ‘/path/*.dbf’ size 100M autoextend on;

2. How to add datafile to existing tablespaces?

alter tablespace tname add datafile ‘/path/*.dbf’ size 100M autoextend on;

3.how to drop a tablespace?

Drop tablespace tname including contents and datafiles;

4. How to drop a datafile?

alter tablespace users drop datafile '/data/oracle/dev/data/userdata1.dbf';

To see tablespaces name and datafiles path?

Select tablespace_name, file_name from dba_data_files;

5. How to rename a tablespace?

alter tablespace oldname rename to newname;

6. How to see datafiles sizes?

Select file_name, bytes/1024/1024 “size in MBs” from dba_data_files;

7. How to see creation time of datafiles;

Select name, creation_time from v$datafile;

8.How to resize a datafile?

Alter database datafile ‘/path/*.dbf’ resize 200M;

How to drop tablespace without datafiles?

Drop tablespace tablespacename;

9.How to add orphan datafile to new tablespace?

Create tablespace tname datafile ‘/path/*.dbf’ reuse;

10. How to add one more orphan datafile to existing tablespace?

Alter tablespace tname add datafile ‘/path/*.dbf’ reuse;

11. How to rename the datafile;

Alter tablespace tname offline;

$mv ‘/path/oldname’ ‘/path/newname’

12.We must give information to controlfile.

Alter database rename file ‘/path/dfname.dbf’ to ‘/path/dfname.dbf’;

alter tablespace tname online;

13. How to create big file tablespace?

Create bigfile tablespace big_ts datafile ‘/path/big_ts.dbf’ size 100M autoextend on;

Temporary tablespace practical’s:--

Views:--

Desc dba_temp_files;

Desc v$tempfile;

Desc dba_tablespace_groups;

select * from dba_tablespace_groups;

Select file_name, file_id, status from dba_temp_files;

Select name, file#, creation_time from v$tempfile;

1. how to create temporary tablespace?

Create temporary tablespace tempname tempfile ‘path/temp.dbf’ size 100M autoextend on;

1.1 How to resize the temporary tempfile?

Sql>alter database tempfile ‘/path/temp.dbf’ resize 100M;

2. how to create tablespace group?

select username, temporary_tablespace, default_tablespace

from dba_users where username = 'TEMPUSER';

Create temporary tablespace temp1 tempfile ‘/path/temp.dbf’ size 100M tablespace group tgrg;

3. how to add existing temp file to group?

Alter tablespace temp2 tablespace group tgrp;

4. how to make temp group as default?

Alter database default temporary tablespace tempgroup;

5. how to drop a tablespace group? First we need to drop members;

alter tablespace temp tablespace group tgrp;

create new temporary tablespace:

create temporary tablespace name tmepfile ‘/path/temp.dbf’ size 100M autoextend on;

make it as default:

alter database default temporary tablespace name

now drop the temporary group members:

drop tablespace tempname including contents and datafiles;

Undo tablespace management:--

Show parameter undo;

Desc v$undostat;

Desc v$rollstat;

We do have only one undo tablespace for one database. When we need more size we will create big one
and replace it.

1. how to create new undo tablespace?

Create undo tablespace undotbs2 datafile ‘/path/undo.dbf’ size 100M;

How to change default undo tablespace?

Alter system set undo_tablespace=’UNDOTBS’ scope=both;

狠狠撸

Tablespaces

Recommended

More Related Content

What's hot (20)

Viewers also liked (8)

Similar to Tablespaces (20)

Tablespaces