Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 5, 2015

Create and manage temporary, permanent, and undo tablespaces



Tablespace Management:

initialization parameters DB_CREATE_FILE_DEST,
DB_CREATE_ONLINE_LOG_DEST_n, or
DB_RECOVERY_FILE_DEST in your initialization parameter file, you instruct Oracle Database to create and manage the underlying operating system files of your database. Oracle Database will automatically create and manage the operating system files.


SHAIKDB>show parameter db_create_file

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest            string



SHAIKDB>show parameter pfile

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                    string     /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/spfileMYDB.ora


Set Datafile destination:

SHAIKDB>alter system set db_create_file_dest='/u01/app/oracle/mydb';

System altered.

SHAIKDB>show parameter create_file_dest

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest            string     /u01/app/oracle/mydb



col name for a40
select ts.name,df.name,bigfile from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;



TSNAME       DFNAME                         BIG
---------- -------------------------------------------------- ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf             NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db             NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf             NO
USERS       /u01/app/oracle/mydb/users01.dbf             NO
f

Create tablespace and add datafiles:


SHAIKDB>create tablespace tbs1;

Tablespace created.


TSNAME       DFNAME                         BIG
---------- -------------------------------------------------- ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf             NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db             NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf             NO
USERS       /u01/app/oracle/mydb/users01.dbf             NO
TBS1       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_tbs1_byp5 NO
      bpjk_.dbf



SHAIKDB>alter tablespace TBS1 add datafile size 100m,size 10m autoextend on maxsize unlimited;

Tablespace altered.

SHAIKDB>select ts.name TSNAME,df.name DFNAME,bigfile from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;



TSNAME       DFNAME                           BIG
---------- ------------------------------------------------------------ ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf               NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db               NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf               NO
USERS       /u01/app/oracle/mydb/users01.dbf               NO
TBS1       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_tbs1_byp5bpjk_.dbf    NO
TBS1       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_tbs1_byp5p042_.dbf    NO
TBS1       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_tbs1_byp5p08f_.dbf    NO

7 rows selected.


Create BIGFILE Tablespace:


SHAIKDB>drop tablespace tbs1 including contents and datafiles;

Tablespace dropped.

SHAIKDB>create bigfile tablespace BIGTBS;      

Tablespace created.

SHAIKDB>select ts.name TSNAME,df.name DFNAME,bigfile from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

TSNAME       DFNAME                           BIG
---------- ------------------------------------------------------------ ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf               NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db               NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf               NO
USERS       /u01/app/oracle/mydb/users01.dbf               NO
BIGTBS       /u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.db YES
      f


Rename a datafile:

SHAIKDB>alter database datafile '/u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.dbf' offline;
alter database datafile '/u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SHAIKDB>archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7




SHAIKDB>alter tablespace bigtbs offline;

Tablespace altered.



SHAIKDB>!mv /u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.dbf /u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf

SHAIKDB>alter database rename file '/u01/app/oracle/mydb/MYDB/datafile/o1_mf_bigtbs_byp5v9f1_.dbf' to '/u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf';

Database altered.


SHAIKDB>alter tablespace bigtbs online;

Tablespace altered.

SHAIKDB>select ts.name TSNAME,df.name DFNAME,bigfile from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

TSNAME       DFNAME                           BIG
---------- ------------------------------------------------------------ ---
SYSTEM       /u01/app/oracle/mydb/sys01.dbf               NO
SYSAUX       /u01/app/oracle/mydb/sysaux01.db               NO
UNDOTBS1   /u01/app/oracle/mydb/undo1.dbf               NO
USERS       /u01/app/oracle/mydb/users01.dbf               NO
BIGTBS       /u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf       YES




SHAIKDB>alter tablespace users nologging;

Tablespace altered.

SHAIKDB>alter tablespace users force logging;

Tablespace altered.

SHAIKDB>alter tablespace undotbs1 retention noguarantee;

Tablespace altered.

SHAIKDB>alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SHAIKDB>alter tablespace undotbs1 retention noguarantee;

Tablespace altered.

Useful views:


V$TABLESPACEName and number of all tablespaces from the control file.
V$ENCRYPTED_TABLESPACESName and encryption algorithm of all encrypted tablespaces.
DBA_TABLESPACES, USER_TABLESPACESDescriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPSDisplays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTSInformation about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTSInformation about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACEInformation about free extents within all (or user accessible) tablespaces.
DBA_TEMP_FREE_SPACEDisplays the total allocated and free space in each temporary tablespace.
V$DATAFILEInformation about all data files, including tablespace number of owning tablespace.
V$TEMPFILEInformation about all temp files, including tablespace number of owning tablespace.
DBA_DATA_FILESShows files (data files) belonging to tablespaces.
DBA_TEMP_FILESShows files (temp files) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAPInformation for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOLFor locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADERShows space used/free for each temp file.
DBA_USERSDefault and temporary tablespaces for all users.
DBA_TS_QUOTASLists tablespace quotas for all users.
V$SORT_SEGMENTInformation about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGEDescribes temporary (sort) segment usage by user for temporary or permanent tablespaces.

No comments: