Column Field too small

[Spirit1]

I have very little experience in running SQL commands.
I ran the following but the I do not know how to increase the report field. Some have #####, Please see below:
SQL> select file_name,
2 sum(bytes)/1024/1024 "Database MB"
3 from dba_data_files
4 group by file_name;

FILE_NAME Database MB
------------------------------------------------------------ -----------
/acct/css/cssfile5a #######
/acct/css/cssfile5b 500.00
/acct/css/cssfile5c 300.00
/acct/css/syst2cssdb.dbf 200.00
/acct/css/systcssdb.dbf 100.00
/acct/css/toolcssdb.dbf 10.00
/acct/css/usrcssdb.dbf 1.00
/acct1/css/cssfile1a #######
/acct1/css/cssfile1b #######
/acct2/css/cssfile1c 250.00
/acct2/css/cssfile1d 250.00
/acct2/css/cssfile2a #######
/acct2/css/cssfile2b 300.00
/acct2/css/cssfile5d 200.00
/acct2/css/cssfile5e 200.00
/acct2/css/syst4cssdb.dbf 300.00
/acct3/css/cssfile5f.dbf 100.00
/acct3/css/cssfile5g 150.00
/acct3/css/cssfile5h 250.00
/acct3/css/rbs2cssdb.dbf 700.00
/acct3/css/rbscssdb.dbf 150.00
/acct3/css/syst3cssdb.dbf 300.00
/acct3/css/temp2cssdb.dbf 200.00
/acct3/css/tempcssdb.dbf 100.00
/acct5/css/cssfile1e #######
/acct5/css/cssfile2c 300.00
/acct5/css/cssfile5i 500.00
/acct5/css/syst4cssdb.dbf 250.00
/acct5/css/temp3cssdb.dbf 250.00
/acct6/css/cssfile1f 500.00

30 rows selected.

 

[SantaMufasa]

Spirit,

The problem you encountered is with SQL*Plus's default width for numeric expressions: 7 characters. And those 7 characters must allow for both a decimal and a minus sign in case the value goes negative. A method to quickly and easily resolve your display issue is to override the default with the following SQL*Plus code (which you execute prior to your SELECT):

col "Database MB" format 999,999,999.99

The resulting column consumes 15 characters of output space.

Let us know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Spirit1]

Mufasa,
Thanks, that worked. Someone sent me a script to use to find out how large my Database was. The reason I need this information is that the Company has decided to convert the Oracle Database on our Unix system to Compulaw which is a Windows SQL database. But I'm getting the following error:

SQL> @compute_db_sizes
unknown command beginning "gram: comp..." - rest of line ignored.
(sum(bytes) over (partition by file_name))/1024/1024
*
ERROR at line 2:
ORA-00907: missing right parenthesis



Segments MB
-----------
11188.9922


Free MB
----------
3772.00781

Here is the script:

set pagesize 50
set linesize 120
column file_name format a60 word_wrap
column "Database MB" format 999.99
clear breaks
break on report
compute sum LABEL 'Total' of "Database MB" on report

select file_name,
(sum(bytes) over (partition by file_name))/1024/1024
AS "Database MB",
round(100*ratio_to_report(bytes) over () , 1 ) pct_total
from dba_data_files
order by file_name;

clear breaks

select sum(bytes)/1024/1024 AS "Segments MB" from dba_segments;

select d.files - s.segments AS "Free MB"
from
(select sum(bytes)/1024/1024 files
from dba_data_files) d,
(select sum(bytes)/1024/1024 segments
from dba_segments) s
;

 

[SantaMufasa]

Spirit,

I don't see the string "gram: comp..." anywhere in your script, so I cannot be helpful there.

If you wish to total up the consumption on your current database, here are a couple of scripts. Script 1, "Usage.sql" displays the current disk consumption of each user/schema. Script 2, "Freespace.sql" displays the disk consumption by tablespace data file. (Note: I have widened the display of this thread to avoid unsightly line wraps.)

Script 1 -- "Usage.sql":

break on report
set pagesize 5000
compute sum of bytes "# exts." on report
col bytes format 999,999,999,999
col "# obj" format 9,999,999
col owner format a30
col latestDDL heading "Most Recent|Stuctural|Modification|Within the|Schema" format a12
select       nvl(sum(bytes),0) bytes
   , decode(nvl(sum(bytes),0)+count(*),1,0,count(*)) "# extents"
   , u.username
   , to_char(o.latestDDL,'dd-MON-YYYY')latestDDL
from         dba_extents e
   , dba_users u
   , (select owner,max(last_ddl_time) latestDDL from dba_objects
           group by owner) o
where u.username = e.owner(+)
  and u.username = o.owner(+)
group by username, latestDDL
order by bytes;
                                                           Most Recent
                                                           Stuctural
                                                           Modification
                                                           Within the
           BYTES  # extents USERNAME                       Schema
---------------- ---------- ------------------------------ ------------
               0          0 DBSNMP
          32,768          2 DONTDROP                       05-JAN-2005
          98,304          6 OUTLN                          27-FEB-2004
       3,866,624         59 TEST                           30-MAR-2005
       5,849,088        331 SYSTEM                         10-DEC-2004
       7,995,392        122 DHUNT                          22-MAR-2005
     151,412,736       1579 SYS                            22-FEB-2005
----------------
     169,254,912

7 rows selected.

Script 2 -- "Freespace.sql":

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt
                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ---------------------------------------------------
DATA1              4    2,097,152,000       83,886,080       11,141,120    2,086,010,880     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2              5    2,097,152,000      241,172,480          851,968    2,096,300,032     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      163,577,856      157,401,088    1,939,750,912     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       10,485,760            8,192    2,097,143,808     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      520,093,696      169,467,904   10,316,292,096


Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
********************************************************************************************************************************************************

Let us know if either of these scripts resolves your need.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Spirit1]

Mufasa,
The first script worked. I tried running the Freespace and got the following error:
SQL> @Freespace.sql
,sys.filext$ e
*
ERROR at line 18:
ORA-00942: table or view does not exist

Here is the script that I ran.

cat Freespace.sql

set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select substr(tablespace_name,1,15) ts
,d.file_id fnum
,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
,d.bytes cb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
decode(e.file#,null,d.bytes,(e.maxextend * blksize))
),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
,''''||substr(file_name,1,55)||'''' fname
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from r
eaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

#

 

[SantaMufasa]

To access "sys.filext$" you need to be either a DBA or have the "SELECT ANY TABLE" privilege. I perhaps improperly presumed that you were a DBA. If you are not a DBA, perhaps you could convince your DBA to at least issue the following privilege, which give you access to the tables you need:

grant select_catalog_role to <your_Oracle_login>;

Let us know how that works.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.