Home » Infrastructure » Other Operating Systems » Space alloc vs space used in Oracle
Space alloc vs space used in Oracle [message #113605] Tue, 16 January 2001 04:55 Go to next message
Pablo Campanini
Messages: 13
Registered: January 2001
Junior Member
Once allocated vsam file and added to tablespace, how can I know how much space is being used??.

Also does unload/SQLPLUS and SQLLOADER more efficient for space saving in a reorg?? than import/export??
Re: Space alloc vs space used in Oracle [message #113614 is a reply to message #113605] Wed, 24 January 2001 12:31 Go to previous messageGo to next message
Jim Gillespie
Messages: 23
Registered: January 2001
Junior Member
: Once allocated vsam file and added to tablespace, how can I know how much space is being used??.

You can execute this code.

select to_char(sum(bytes),'999,999,999,999,999') "FREESPACE",
to_char(max(bytes),'999,999,999,999,999' "LARGEST EXTENT"
from dba_free_space
where tablespace_name = 'YOUR TABLESPACE';

There are about 723,000 bytes per cylinder, so you could divide the sum by that and get the approximate number of free cylinders.

: Also does unload/SQLPLUS and SQLLOADER more efficient for space saving in a reorg?? than import/export??

SQLLOADER in direct mode is much faster than import. Spooling with SQLPLUS may not be as fast as direct EXPORT, but it does have one advantage. If you want to load the data in a specific order, you can spool it out and use a sort utility to put your data in order.
Re: Space alloc vs space used in Oracle [message #113652 is a reply to message #113605] Thu, 22 March 2001 11:58 Go to previous messageGo to next message
Scott Graham
Messages: 3
Registered: March 2001
Junior Member
I use the following sql to give me info for space utilization
in blocks for the vsam file(s) and the tablespace within the vsam file.
The databases we have are small, so I can run this sql
against the whole database and it come back fairly
quickly.
set pagesize 22
column tablespace_name format a20
column fileblocks heading 'File|Blocks'
column tablespace_blocks heading 'Used|Tablespace|Blocks'
column allocatedblocks heading 'Allocated|Tablespace|Blocks'
column emptyblocks heading 'Empty|Tablespace|Blocks'
select t.tablespace_name, sum(f.blocks) fileblocks,
sum(t.blocks)+sum(t.empty_blocks) allocatedblocks,
sum(t.blocks) tablespace_blocks, sum(t.empty_blocks) emptyblocks
from dba_data_files f, dba_tables t
where f.tablespace_name = t.tablespace_name
group by f.tablespace_name, t.tablespace_name
order by f.tablespace_name, t.tablespace_name
empty field in oracle [message #113880 is a reply to message #113652] Mon, 17 March 2003 20:56 Go to previous message
ibbrahim
Messages: 3
Registered: February 2003
Junior Member
i need a empty colomn in oracle
In sql i know i can use SELECT '' ) AS Advocate_Name1, but in oracle it giving from keyword is missing help me

thanx
ibbrahim
Previous Topic: Oracle 8.1.5 Netware 5.1 sp5
Next Topic: import on mvs with oracle 9i
Goto Forum:
  


Current Time: Mon Apr 21 08:07:53 CDT 2014

Total time taken to generate the page: 0.08524 seconds