Oracle DB: Data is displayed as #

user_13046685 0
Viewed 33

I am working on Oracle DB in the Linux environment and I wrote the following script:

col TABLESPACE_NAME format a30;
col username format a30;
col bytes format a30;
col max_bytes format a20;
col blocks format a20;
col max_blocks format a20;
col dropped format a20;

set wrap off;
set linesize 3000;
set pagesize 500;

Select
    TABLESPACE_NAME
    , username
    , bytes
    , max_bytes
    , blocks
    , max_blocks
    , dropped
FROM
    DBA_TS_QUOTAS;

However, the bytes, max_bytes, block, max_blocks data is displayed as '###', how do i fix this issue such that data is actually displayed? enter image description here

Thanks in advance

oracle
1 Answers

The number fields should be expressed as numbers. column and format should match what you are trying to display

I would user col xxxx format 9999999999999999 for fields as bytes, max_bytes or any other number field. If you are still having #### , try to increase the number of 9 in the format clause.

col TABLESPACE_NAME format a30;
col username format a30;
col bytes format 99999999999999;
col max_bytes format 99999999999999;
col blocks format 99999999999999;
col max_blocks format 99999999999999;
col dropped format a20;

set wrap off;
set linesize 3000;
set pagesize 500;

Select
    TABLESPACE_NAME
    , username
    , bytes
    , max_bytes
    , blocks
    , max_blocks
    , dropped
FROM
    DBA_TS_QUOTAS;

Example

SQL> col TABLESPACE_NAME format a30;
SQL> col username format a30;
SQL> col bytes format 99999999999999;
SQL> col max_bytes format 99999999999999;
SQL> col blocks format 99999999999999;
SQL> col max_blocks format 99999999999999;
SQL> col dropped format a20;

SQL> set wrap off;
SQL> set linesize 3000;
SQL> set pagesize 500;
SQL> Select
        TABLESPACE_NAME
        , username
        , bytes
            , max_bytes
            , blocks
            , max_blocks
        , dropped
    FROM
    DBA_TS_QUOTAS;

TABLESPACE_NAME                USERNAME                               BYTES     MAX_BYTES         BLOCKS   MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ------------- ------------- -------------- ------------ --------------------
TBOUT_202206                   FDM_OUTPUT                            393216            -1             24           -1 NO
TBOUT_202210                   FDM_OUTPUT                            393216            -1             24           -1 NO
TBFCDSTORE                     FCD_OUT                           3285123072            -1         200508           -1 NO
TBALMSTORE                     DMS_ALM                           1696595968            -1         103552           -1 NO
TBRDMSTORE                     DMS_RDM                             68878336            -1           4204           -1 NO
TBCDR_202101                   DMS_CDR                            277938176            -1          16964           -1 NO
TBDAT_202001                   FDM_DATA                            19595264            -1           1196           -1 NO
TBDAT_202004                   FDM_DATA                            12189696            -1            744           -1 NO
TBCPTSTORE                     DMS_CPT                             17629184            -1           1076           -1 NO
TBCPT_202002                   DMS_CPT                             67108864            -1           4096           -1 NO
user_137555380
Related