Oracle DB: Data is displayed as #-CodePudding

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?

Thanks in advance

CodePudding user response：

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