1748126 Members
3154 Online
108758 Solutions
New Discussion юеВ

Re: schema object

 
SOLVED
Go to solution
HPADM
Advisor

schema object

Hi,
I tried to create a schema object for ANDREA user with a object ( TABLE):

Is it right?

SQL> create user "ANDREA"profile "DEFAULT" identified by "lazio"
2 default tablespace "PROVA_LOCAL" temporary tablespace "TEMP";

SQL> grant create session to "ANDREA";

Grant succeeded.

SQL> grant connect to "ANDREA";

Grant succeeded.

SQL> grant unlimited tablespace to "ANDREA";

Grant succeeded.

SQL> grant resource to "ANDREA";

Grant succeeded.

SQL> grant create any view to "ANDREA";

Grant succeeded.


Creazione tabella:


SQL> create table STRANO(
2 citta varchar2(13) not null,
3 datacampione date not null,
4 precipitazione number
5 );

Table created.

SQL> commit;

Commit complete.

SQL> select object_name, object_type from all_objects where owner = 'ANDREA';

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
STRANO TABLE


I created the TABLE STRANO in the PROVA_LOCAL tablespace, how can I see the free space in that tablespace?

thank you
4 REPLIES 4
Ian Lochray
Respected Contributor

Re: schema object

log on to sqlplus as system and execute ...
select sum(bytes) from dba_free_Space where tablespace_name = 'PROVA_LOCAL';
John Palmer
Honored Contributor

Re: schema object

select sum(bytes) from user_free_space
where tablespace_name = 'PROVA_LOCAL';

will give you the total number of free bytes in that tablespace.

select bytes from user_free_space
where tablespace_name = 'PROVA_LOCAL';

will give you a list of the size of each free extent.

Is this what you want?

Ravi_8
Honored Contributor
Solution

Re: schema object

Hi,

TABLESPACE USAGE NOTES:


Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc


never give up
Yogeeraj_1
Honored Contributor

Re: schema object

hi,

Yes, the following piece of SQL will give you the information desired (in MB):

select sum(bytes)/1024/1024 "Free in MB" from user_free_space
where tablespace_name = 'PROVA_LOCAL';

You may also wish to get all the free space available in all tablespaces. Run the attached script using an oracle account having "select all tables" role or dba.

Hope this helps!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)