APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
MAIN CONTENT
--- Purpose ---
This Master Note is intended to provide a jumping off point for tablespace topics and issues in the Oracle RDBMS, including:
* A list of relevant notes to progress issues, and helpful tips for filing Service requests if needed.
--- Concepts ---
A Locally Managed tablespace (LMT) is a tablespace that manages its own extents by
maintaining a bitmap in each datafile to keep track of the free or used status of blocks
in that datafile. This is the default for new permanent tablespaces (in 10g and up), but you must specify the EXTENT MANAGEMENT LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).
A Data Dictionary Managed tablespace is managed using dictionary tables. Oracle no longer recommends Data Dictionary managed tablespaces as they are a known source of potential performance bottlenecks due to the ST-[space management] enqueue.
Automatic Segment Space Management (ASSM) tablespace automates freelist management and removes the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes created in these tablespaces. Automatic Segment Space Management (ASSM) is enabled by default in Oracle 10g and up.
Full documentation and coverage of Tablespace overview is covered in the following guides:
Oracle? Database Concepts 10g Release 2 (10.2)
Oracle? Database Concepts 11g Release 1 (11.1)
Oracle? Database Concepts 11g Release 2 (11.2)
--- How-To ---
Tablespace creation:
The only mandatory parameter to create a tablespace in CREATE TABLESPACE statement is its name.
CREATE TABLESPACE;
The created tablespace will be:
- Permanent, locally managed and with system allocated extent size.
- Datafile will be created in the location provided in the DB_CREATE_FILE_DEST parameter with a size of 100 MB. The datafile will be autoextensible.
- The name of the datafile will be similar to "o1_mf_SQL> select tablespace_name, contents, allocation_type, extent_management from dba_tablespaces; _.dbf"
Complete Syntax
CREATE [BIGFILE /| SMALLFILE] [TEMPORARY / UNDO] TABLESPACE <TABLESPACE>
DATAFILE / TEMPFILE '<datafile path and name>' SIZE <integer M>[,
'<datafile N path and name>' SIZE <integer M>[,...]]
BLOCKSIZE <DB_BLOCK_SIZE parameter /2k/4k/8k/16k/32k >
AUTOEXTEND { [OFF/ON (NEXT MAXSIZE) / UNLIMITED] }
LOGGING/NOLOGGING (Logging default)
ONLINE/OFFLINE (Online default)
EXTENT MANAGEMENT { [DICTIONARY] / [LOCAL Default (AUTOALLOCATE / UNIFORM )] }
SEGMENT SPACE MANAGEMENT [AUTO / MANUAL]
PERMANENT / TEMPORARY (Permanent default)
MINIMUM EXTENT
DEFAULT STORAGE { [INITIAL ]
[NEXT ]
[PCTINCREASE ]
[MINEXTENTS ]
[MAXEXTENTS / UNLIMITED]
[FREELISTS ]
[FREELIST GROUPS ]
[OPTIMAL /NULL]
[BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] }
CHUNK
NOCACHE;
Full documentation and coverage of Tablespace creation and syntax is covered in the following guides:
Oracle? Database SQL Reference 10g Release 2 (10.2)
Oracle? Database SQL Language Reference 11g Release 1 (11.1)
Oracle? Database SQL Language Reference 11g Release 2 (11.2)
--- Best Practices ---
-
Use locally managed tablespaces (not dictionary managed).
-
Do not create objects in the SYSTEM tablespace. The system tablespace is reserved for the data dictionary.
-
Avoid creating tablespaces, with hundreds of small datafiles, as these files need to be checkpointed, resulting is unnecessary processing.
--- Troubleshooting a Tablespace Issue ---
This Master Note is provided to assist you in the solving of issues and logging of SRs for Tablespace questions and issues. The method provided below is based on Oracle Diagnostic Methodology (ODM) and helps you to avoid unnecessary trial and error by guiding you through a step-by step method. Diagnostic tools, examples and general questions are included in the document to assist in the different troubleshooting steps.
Step by step approach
Issue Clarification ==> Issue Verification ==> Cause Determination ==> Potential Solutions
In the issue clarification section it is important to describe the problem as best as possible. What is the problem you need to solve? At the end of the process you should be able to come back to this section to verify if the root cause was found and the solution was provided.
WHAT IS the problem we are looking at?
Examples of problem description:
- An ORA-1652 - Unable to extend temp segment by %s in tablespace %s - is received on a temporary tablespace that has autoextend enabled on one or more of its tempfiles.
- Receiving critical space alerts from EM Console for a key application tablespace, even though, dba_free_space shows 50% of the space is free for this tablespace.
Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions
When you have a starting problem description it is time to collect facts in a structured way. First step would be to get an overview of all the facts we have. What information do we have already available? The answers to the questions below can potentially help you to solve the problem.
What is the problem / what is not a problem?
What are the symptoms?
What are the different errors generated?
What command produces the problem?
Under what conditions does the issue occur?
What 5-digit version of Oracle software reproduces the problem?
- (For Windows platform confirm patch number bundle used)
What 5-digit version of Oracle software works?
- (For Windows platform confirm patch number bundle used)
When is the problem seen / when is the problem not seen?
Is the problem constant?
Is the problem reported intermittently?
Is there any pattern to the failure?
What resolves the problem?
How long does the problem last?
Has this ever worked or is this a fresh installation / setup?
When did the problem start to fail??
Where is the problem / Where is the problem not?
Does problem reproduce on another node / server?
Does problem reproduce against another database?
Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions
The facts listed in the ISSUE VERIFICATION are the starting point for the CAUSE DETERMINATION:
There are 3 main approaches to take here:
1. Use your experience to list possible causes. List the assumptions that need to be checked in this case.
2. Start searching for possible causes in My Oracle Support or other Oracle knowledge bases. Use the facts collected above to refine your search criteria.
3. Analyze the facts on differences between the working situation and non working situation: Depending on the answers from the questioning above and further investigation from the troubleshooting guides you should be able to list what is different, special, unique between the IS and the IS NOT and also see what is changed and when.
What changes happened around time the first failure / problem was reported?
* New software installed?
* Upgrade done?
* Configuration changes?
* What is different between the methods that work and those that do not?
* What changed and could have an impact between the working situation and the current situation?
Searching tips and tricks:
You can search or browse in our repository for issues.
For example:
ORA-1652 : unable to extend temp segment by %s in tablespace %s
Search on ORA-1652 + temp segment + symptoms
Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions
A brief description of the corrective actions that will remove the cause of the problem: in some cases there is only one solution linked to the cause. But in many cases, there are more. Example: install a patch to remove the bug from the system, avoid the problem by creating a different job setup, or avoid the problem by setting some parameters.
Determine the most probable cause and a relevant solution.
--- SR Creation ---
If you are blocked in the resolution process of your problem it is best to log a Service Request (SR).
When logging a SR please provide all the information you collected in the previous steps. The support engineer serving you will follow a very similar approach to come to the resolution of your problem. The more detailed you can describe the problem by using your issue clarification and verification, the better we will be able to solve you.
--- Diagnostic Tools ---
* Collection of the relevant information using selects from the dictionary views, showing the current states and used attributes.
Example: Query V$DATABASE, V$INSTANCE
* Alert.log and trace files
* RDA report
--- Generic Diagnostics ---
Tablespace information:
SQL> select tablespace_name, contents, allocation_type, extent_management from dba_tablespaces;
To check for current TEMP usage:
select tablespace_name, round(sum(bytes_cached)/1024/1024/1024) GB from v$temp_extent_pool group by tablespace_name;
Example query to check free and used space per tablespace:
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
轉到底部