-
tablespace space
I created a tablespace like this:
CREATE TABLESPACE "PRI_TABLES"
DATAFILE '/oracle/data/pri/pri_table.dbf' SIZE 50M
autoextend on next 50M maxsize 500M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
As you can see I use the maxsize parm.
When I run the following query:
set pagesize 80
set linesize 80
col tablespace_name for a18
COL total for 999,999 heading "MB|Total"
COL free for 999,999 heading "MB|Free"
COL max_free for 999,999 heading "MB|Extent|MaxFree"
col used_pct for 990.99 heading "Used|percnt"
col free_cnt for 9999 heading "Free|Extent|Count"
select a.tablespace_name,round(100-b.free/a.total*100,2) Used_pct,
a.total,b.free,b.max_free, b.free_cnt from (select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 free, max(bytes)/1024/1024 max_free, count(bytes) free_cnt from dba_free_space
group by tablespace_name) b where a.tablespace_name=b.tablespace_name;
I get this output:
MB Free
Used MB MB Extent Extent
TABLESPACE_NAME percnt Total Free MaxFree Count
------------------ ------- -------- -------- -------- ------
SYSTEM 54.50 400 182 182 2
PRI_TABLES 54.38 50 23 22 4
I would like "MB Total" to reflect maxsize I told it to grow too instead of the
initial size I set it too. Where would I find that information (maxsize)?
My rational is that I really said the TS should be 500MB but I am only using
50M at a time until I need the space.
If somebody can point me in the right direction or alter my query I would
appreciate it.
Thanks in advance to all who answer.
-
The information you are looking for is stored in MAXBYTES column if and only if column AUTOEXTENSIBLE=YES
For future reference, when posting code please wrap it up in code-tags so to make it readable... Go Advanced... user "#" code tags, like...
Code:
select tablespace_name
,file_name
,bytes
,autoextensible
,maxbytes
from dba_data_files
where rownum < 10;
Last edited by PAVB; 08-19-2010 at 09:43 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|