Create and manage temporary, permanent, and undo tablespaces

1. Documentation in Tahiti -> Masters Book List -> Administrator’s Guide -> 14 Managing Tablespaces

2. Summary of the different settings when creating permanent tablespace

TABLESPACE MANAGED LOCALLY | DICTIONARY TABLESPACE MANAGED
————————————– ———— ————————–
| AUTOMATIC SEGMENT | USER SEGMENT | extents are managed through |
| SPACE MANAGEMENT | Space Management | Data Dictionary |
| (ASSM) | (MSSM) | (Obsolete) |
——————- —————— ————- ————————-
| Use Bitmaps | Use FREELISTS | Use Dict. => SYS.EUT SYS.FET $ and $ |
——————- —————— ————- ————————-

3. We started creating different types of permanent TABLESPACE

- Create a TBS without any option to see how it creates default
- This statement is equivalent to this other
- CREATE TABLESPACE TEST01 DATAFILE '/u01/app/oracle/oradata/OCM/test01.dbf' SIZE 100M
- SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

  CREATE TABLESPACE TEST01 DATAFILE '/u01/app/oracle/oradata/OCM/test01.dbf' SIZE 100M;

- We check how it has created this TABLESPACE (TEST01) and TBS review the rest of the BD
- By default, if no specific properties => LOCALLY MANAGED + + AUTOALLOCATE ASSM (SYSTEM)
SELECT
  TABLESPACE_NAME,
  BLOCK_SIZE,
  EXTENT_MANAGEMENT,
  SEGMENT_SPACE_MANAGEMENT,
  ALLOCATION_TYPE,
  Bigfile
FROM
  DBA_TABLESPACES;

- Now create a TBS LOCALLY MANAGED + ASSM + ALLOCATE UNIFORM (1M extents)
CREATE TABLESPACE TEST02 DATAFILE '/u01/app/oracle/oradata/OCM/test02.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

- Create a third TBS LOCALLY MANAGED + MSSM + AUTOALLOCATE
CREATE TABLESPACE TEST03 DATAFILE '/u01/app/oracle/oradata/OCM/test03.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT MANUAL EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

- By default, TBS created with DATAFILES in NOAUTOEXTENSIBLE mode, that is, its size is fixed
- Create quarter DATAFILE CanGrow TBS with a maximum size of 2G
CREATE TABLESPACE TEST04  DATAFILE '/u01/app/oracle/oradata/OCM/test04.dbf' AUTOEXTEND ON NEXT SIZE 100M 2G MAXSIZE 100M;

- With MSSM, Oracle uses FREELISTS to know that blocks are free

- With ASSM, Oracle is responsible for self-management in the blocks using bitmaps

- There is a crucial parameter PCTFREE, which specifies the free space for future UPDATES both ASSM and MSSM

- We will make a test filling two tables with different values ​​for PCTFREE and see what deal
CREATE TABLE TEST01 TABLESPACE TEST_PCTFREE_0 PCTFREE 0 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST01 TEST_PCTFREE_50 TABLESPACE PCTFREE 50 AS SELECT * FROM DBA_OBJECTS;
- We statistical tables
ANALYZE TABLE TEST_PCTFREE_0 COMPUTE STATISTICS;
ANALYZE TABLE TEST_PCTFREE_50 COMPUTE STATISTICS;
- We check as different values ​​for PCTFREE can cause different occupations for a table
SELECT
  TABLE_NAME,
  NUM_ROWS,
  BLOCKS,
  AVG_SPACE,
  PCT_FREE
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('TEST_PCTFREE_0' 'TEST_PCTFREE_50');

4. So far we are specifying the location of each TBS DATAFILES we create. But we can use Oracle Managed Files (OMF) if we want to avoid this task.

- We need to specify the default path for OMF
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata/OCM/' SCOPE = BOTH;

- Create fifth TBS without specifying the location of your datafiles
- This creates an auto-extending datafile only 100M and maximum size of 32GB
CREATE TABLESPACE TEST05;

- We check the characteristics of DATAFILE created
SELECT
   FILE_NAME,
   BYTES / 1024/1024 "MB"
   CanGrow,
   MAXBYTES / 1024/1024 "MB"
FROM
   DBA_DATA_FILES
WHERE
   TABLESPACE_NAME = 'TEST05';

5. In the Target “Determine and September sizing parameters for database structures” have seen TBS create different block size.

- Let's do another quick test creating a TBS with 16K block size
ALTER SYSTEM SET DB_16K_CACHE_SIZE SCOPE = BOTH = 50M;
CREATE TABLESPACE TEST06 BLOCKSIZE 16K;

6. The last exercise we will do permanent TBS is to create a TABLESPACE bigfile. The advantage of using TBS bigfile is that we have huge DATAFILES (if we use 8k block size can be up to 32TB). To this utility you see him when you have a DB 40,000 datafiles and it takes 30 min. in lift because you have to go all.

- Create a TBS clause indicating bigfile
CREATE bigfile TABLESPACE TEST07 DATAFILE '/u01/app/oracle/oradata/OCM/test07.dbf' SIZE 100M;

- We again see the characteristics of all created TBS
SELECT
   TABLESPACE_NAME,
   BLOCK_SIZE,
   EXTENT_MANAGEMENT,
   SEGMENT_SPACE_MANAGEMENT,
   ALLOCATION_TYPE,
   bigfile
FROM
   DBA_TABLESPACES;

7. We clean the test environment with permanent TBS.

– We erased the TBS and its contents
DROP TABLESPACE INCLUDING CONTENTS TEST01 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST02 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST03 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST04 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST05 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST06 AND DATAFILES;
DROP TABLESPACE INCLUDING CONTENTS TEST07 AND DATAFILES;

– We revert the parameters that have changed
ALTER SYSTEM RESET DB_16K_CACHE_SIZE SCOPE = SPFILE;
ALTER SYSTEM SET DB_16K_CACHE_SIZE SCOPE = MEMORY = 0;
ALTER SYSTEM RESET DB_CREATE_FILE_DEST SCOPE = SPFILE;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = ” SCOPE = MEMORY;

8.. Let TBS tests Temporary (TEMPORARY TABLESPACE).

– The first thing we do is see what the temporary default TBS
SELECT PROPERTY_NAME, property_value DATABASE_PROPERTIES FROM WHERE LIKE PROPERTY_NAME ‘DEFAULT_TEMP_TABLESPACE’;

9. Let’s create a group of temporary TBS TBS adding TEMP and create a new one now.

- Add the current temporary TBS (TEMP) to a new group (TEMPGROUP)
ALTER TABLESPACE TEMP TABLESPACE GROUP TEMPGROUP;
- Confirm the operation
SELECT * FROM DBA_TABLESPACE_GROUPS;

- Create a second temporary TBS, and add it directly to grup in the same setencia
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/OCM/temp201.dbf' SIZE 100M TABLESPACE GROUP TEMPGROUP;
- Validate the operation
SELECT * FROM DBA_TABLESPACE_GROUPS;

- You can also specify the temporary group temporary TBS TBS as default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGROUP;
- We review the change
SELECT PROPERTY_NAME, property_value DATABASE_PROPERTIES FROM WHERE LIKE PROPERTY_NAME 'DEFAULT_TEMP_TABLESPACE';

- Revert all changes
ALTER TABLESPACE TEMP TABLESPACE GROUP '';
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE INCLUDING CONTENTS TEMP2 AND DATAFILES;

10. In the 11g version of Oracle can reduce the temporary tablespace. We can even set a minimum reserved space.

- Extended the temporary TBS before cutting it for testing
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/OCM/temp01.dbf' RESIZE 200M;
- We can check the size with the following query
SELECT BYTES / 1024/1024 FROM DBA_TEMP_FILES;

- Cut the temporary tablespace to a limit of 10M and consulted size
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 10M;
SELECT BYTES / 1024/1024 FROM DBA_TEMP_FILES;

- Cut the maximum temporary TBS and reviewed the space it occupies now
ALTER TABLESPACE TEMP SHRINK SPACE;
SELECT BYTES / 1024/1024 FROM DBA_TEMP_FILES;

11. It is sometimes useful to see the space used by temporary TBS different users of the database.

- We can see a quick summary of the total space and free space allocated by sight DBA_TEMP_FREE_SPACE
SELECT * from DBA_TEMP_FREE_SPACE;

- If we get the space used by temporary TBS session, we can use V $ SORT_SEGMENT and V $ TEMPSEG_USAGE
- From another session we launched a consultation that needs large space for sorts
SELECT A.OBJECT_ID FROM DBA_OBJECTS A, B DBA_OBJECTS ORDER BY OBJECT_ID;
- While performing the sort we see the occupation of the session in the temporary TBS (TBS remember that this is 8k = 8192)
SELECT USER, SESSION_NUM "SERIAL #" SQL_ID, BLOCKS * 8192/1024/1024 "MB" FROM V$TEMPSEG_USAGE;

- We can cancel the query and cut temporary TBS
ALTER TABLESPACE TEMP SHRINK SPACE;

12. Now we are going to exercise with TBS UNDO

Documentation Tahiti -> Masters Book List -> Administrator’s Guide -> 16 Managing Undo

13. We have three fundamental parameters that control the management and retention of UNDO TBS

– We show the values of these three parameters
– UNDO_MANAGEMENT = AUTO => is the default. Oracle manages undo segments
In MANUAL mode rollback segments would be used -> Not Recommended
– UNDO_RETENTION = 900 => Time Oracle tries to retain UNDO information (seconds)
– UNDO_TABLESPACE = UNDOTBS1 => name of UNDO TBS. If you do not specify Oracle uses the first one it finds
show parameter undo

14. UNDO TBS size and recommended retention vary DB. One way to estimate the size of TBS UNDO retention is set to a value of 5-6 hours and check the size of TBS.

– We expanded retention (5h 60min * * 60sec = 18000)
– When UNDO DATAFILES TBS are not AUTOEXTEND mode, the parameter is ignored UNDO_RETENTION
ALTER SYSTEM SET UNDO_RETENTION SCOPE = BOTH = 18000;

– After 5 hours, observe the size of TBS UNDO and eliminate the AUTOEXTEND of datafiles
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/OCM/undotbs01.dbf’ AUTOEXTEND OFF;

– Another quick way to get a recommendation is to use the V$UNDOSTAT
– First we get the peak UNDO used per second
SELECT MAX (UNDOBLKS / ((END_TIME-BEGIN_TIME) * 24 * 60 * 60)) “MAXUNDOPERSEC” FROM V $ UNDOSTAT;
– After the Query get more runtime
SELECT MAX (MAXQUERYLEN) FROM V $ UNDOSTAT;
– The recommended value is MAXUNDOPERSEC * * DB_BLOCK_SIZE MAXQUERYLEN
– We can do it all in one visit
SELECT
(SELECT MAX (UNDOBLKS / ((END_TIME-BEGIN_TIME) * 24 * 60 * 60)) “MAXUNDOPERSEC” FROM V $ UNDOSTAT) *
(SELECT MAX (MAXQUERYLEN) FROM V $ UNDOSTAT) *
(SELECT FROM V $ PARAMETER DISPLAY_VALUE WHERE NAME = ‘db_block_size’) / 1024/1024 “UNDO ADVICE (MB)”
FROM
DUAL;

– You can also get this information with the package DBMS_UNDO_ADV

15. We may also use the UNDO ADVISOR Enterprise Manager or through the interface. When you install Grid Control will test through web. Now we will see how to do it using DBMS_ADVISOR.

– First we have to get the range of snapshots of where we want to study
– For this we get the list of AWR snapshots and choose those that interest us for the time window
– I’ll pick the last two SNAP_ID corresponding to the last hour (228 and 229)
SELECT SNAP_ID ,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 2;

– Launched the ADVISOR. Notice the SNAPSHOTS we have chosen, 228 and 229
DECLARE
NUMBER tid;
tname VARCHAR2 (30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK (‘Undo Advisor’, tid, tname, ‘Undo Advisor Task’);
DBMS_ADVISOR.CREATE_OBJECT (tname, ‘UNDO_TBS’, null, null, null, ‘null’, oid);
DBMS_ADVISOR.SET_TASK_PARAMETER (tname, ‘TARGET_OBJECTS’ oid);
DBMS_ADVISOR.SET_TASK_PARAMETER (tname, ‘START_SNAPSHOT’, 228);
DBMS_ADVISOR.SET_TASK_PARAMETER (tname, ‘END_SNAPSHOT’, 229);
DBMS_ADVISOR.SET_TASK_PARAMETER (tname, ‘instance’, 1);
DBMS_ADVISOR.execute_task (tname);
END;
/

– We can keep track of the execution of the task DBA_ADVISOR_TASKS
SELECT TASK_NAME, DBA_ADVISOR_TASKS STATUS FROM WHERE DESCRIPTION = ‘Undo Advisor Task’;

– Once you have finished we can take the inquiries (FINDINGS)
– The name of the task (TASK_NAME) him out of the previous query
– It is much more convenient to use the Enterprise Manager (EM), but we’ll see later
SELECT * FROM WHERE DBA_ADVISOR_FINDINGS TASK_NAME = ‘TASK_329’;

16. At any time can check using UNDO through DBA_UNDO_STATS view.

– An extent of UNDO can be active or unexpired EXPIRED
– Unexpired => reusable EXTENTS only if the UNDO TBS is so NOGUARANTEE
– EXPIRED => EXTENTS reusable UNDO anyway
– ACTIVE => UNDO currently in use
SELECT STATUS, SUM (BYTES) / 1024/1024 MB FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

17. We have seen in the previous point that the TBS UNDO can be NOGUARANTEE GUARANTEE or so. GUARANTEE activate the mode when you want to finish some very heavy sentences at the expense of others fail (ORA-30036). When you run a heavy sentence and fails because the UNDO you need has been reused failure by the ORA-1555 error.

# I will not go into the ORA-1555 and ORA-30036 errors because there are thousands of websites that talk about it
# When you meet an error, you can use the “OERR” utility to get more information that the error message itself
We pray OERR 1555 # UNDO_RETENTION parameter suggests increase or extend the UNDO
We pray OERR 30036 # TBS suggests expanding the UNDO

– If necessary we can put the TBS in GUARANTE UNDO / NOGUARANTEE mode very easily
ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
– We disabled the mode GUARANTEE
ALTER TABLESPACE UNDOTBS1 NOGUARANTEE RETENTION;

18. Now let’s change the UNDO TBS. So we must create a new TBS and make the change.

– Create a TBS UNDO
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘/u01/app/oracle/oradata/OCM/undotbs201.dbf’
AUTOEXTEND ON NEXT SIZE 100M 10M MAXSIZE UNLIMITED;

– Changes parameter undo_tablespace = UNDOTBS2
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE = BOTH;

– We delete the old TBS
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

– Will reverse the change and add one seconds DATAFILE to TBS UNDO
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ‘/u01/app/oracle/oradata/OCM/undotbs01.dbf’
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1 SCOPE = BOTH;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE ‘/u01/app/oracle/oradata/OCM/undotbs02.dbf’
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.