This is an old revision of the document!
Table of Contents
Tablespaces
Update all datafiles in the database to be autoextend on and maxsize unlimited
A quick fix to ensure all tablespaces can be extended to the max. Have to keep an eye on the filesystem growth now.
0@@
User quotas on all tablespaces
col quota format a10 select username , tablespace_name , decode ( max_bytes
, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024)||'M'
) quota
from dba_ts_quotas where 1=1 and tablespace_name not in ('TEMP');
Tablespace Growth Report 1
1@@
Tablespace Growth Report 2
The period range for this report is dependent on the snapshot retention period set for the db<br /> http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx Original page
2@@
Tablespace Growth Report 3
Original page<br /> You probably have a problem with the autoextend max_size if column 5 is less than column 8!
3@@
If you are stuck on software written last century:
4@@
Segment Growth Report
5@@
What is the size of the segments in the SYSAUX tablespace?
6@@
What are the components that make up the SYSAUX tablespace?
7@@
Alert tablespace with least space available
8@@
Which sessions are using TEMP tablespace?
9@@
What is the currently assigned default temporary tablespace?
See DEFAULT_TEMP_TABLESPACE to see which TEMP table space is defaulted at database level
10@@
Reduce (or recreate) size of temporary tablespace
Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.<br /> Create a temporary temp tablespace
11@@
Assign this new temporary temp tablespace to the database
12@@
Check for any sessions still holding on to the TEMP tablespace
13@@
Drop the original temp tablespace
14@@
Recreate the original temp tablespace (smaller, or with fewer datafiles)
15@@
Assign the new temp tablespace to the database
16@@
Drop the temporary temp tablespace
17@@
Recreate the temporary tablespace when it belongs to a tablespace group
Verify the tablespace groups and their members
18@@
Check the filenames for these tablespaces
19@@
Create new tablespaces and assign them to a new group
20@@
Recheck the groups
21@@
Make the new tablespace group the default at database level
22@@
Check for any sessions still using the old default temporary tablespace (group)
23@@
Kill any sessions that may be killed
24@@
Remove the old tablespaces from the tablespace group and reassign the new tablespaces to it
25@@
Drop the old tablespaces
26@@
Check the final situation
27@@
Which tablespaces are encrypted
Encrypt tablespaces to protect data (after having created a wallet)
28@@
Check encryption algorithm
29@@