topic Re: schema object in Operating System - HP-UX

schema object

HPADM — Tue, 10 Dec 2002 11:08:07 GMT

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

Re: schema object

Ian Lochray — Tue, 10 Dec 2002 11:20:56 GMT

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

Re: schema object

John Palmer — Tue, 10 Dec 2002 11:26:35 GMT

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?

Re: schema object

Ravi_8 — Tue, 10 Dec 2002 11:40:15 GMT

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

Re: schema object

Yogeeraj_1 — Tue, 10 Dec 2002 11:44:02 GMT

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