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