Menu
I need to check space used by a tablespace but I have no dba privs. Is there a way to do this?
470
When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what's there and how much space are free to use. SELECT df. tablespace_name "Tablespace", totalusedspace "Used MB", (df.
Enter the schema name and object name (optional) and click Go. Use the radio button to select the table you want to look at and click on Edit (Don't click on the table name link) Click on the Segments tab (and wait...) You will see the size of the table data and the indexes used.
totalusedspace)/ d. totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) d, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) s where d. tablespace_name = s.
Here is a query to find users that have unlimited quota on a tablespace. Here is a query to find users that have unlimited quota on a tablespace. select username,tablespace_name,max_bytes from dba_ts_quotas; If the max_bytes values is -1, that user has unlimited quota on the corresponding tablespace.
Unfortunately without explicit permissions to the dba_free_space or dba_segments views you are stuck with your users default tablespace:
SELECT
ts.tablespace_name,
TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
user_free_space fs,
user_tablespaces ts,
user_users us
WHERE
fs.tablespace_name(+) = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
ts.tablespace_name;
If you need to check the size of a tablespace for which you don't have a user with that as their default tablespace you're stuck with going back to your DBA.
Test with the system tablespace as default:
Test with an app tablespace as the default tablespace:
This schema does not have query on the dba views:
select * from dba_free_space;
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 13 Column: 15
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
