Column formatting SQL Plus

Column output in SQL Plus

Adding for reference.

Link to http://www.adp-gmbh.ch/ora/sqlplus/column.html

column [SQL*Plus]


SQL> column colum_name alias alias_name
SQL> column colum_name clear

SQL> column colum_name entmap on
SQL> column colum_name entmap off

SQL> column colum_name fold_after
SQL> column colum_name fold_before
SQL> column colum_name format a25
SQL> column colum_name heading header_text

SQL> column colum_name justify left
SQL> column colum_name justify right
SQL> column colum_name justify center

SQL> column colum_name like expr|alias
SQL> column colum_name newline
SQL> column colum_name new_value variable
SQL> column colum_name print 
SQL> column colum_name noprint 
SQL> column colum_name old_value 

SQL> column colum_name on 
SQL> column colum_name off 

SQL> column colum_name wrapped 
SQL> column colum_name word_wrapped 
SQL> column colum_name truncated 
The command can be used to make the output prettier 

format

Specifies the format for a column (that is, how the data of the column is printed).
column column_name format a20
column column_name format a50 word_wrapped
column column_name format 999.999  -- Decimal sign
column column_name format 999,999  -- Seperate thousands
column column_name format $999     -- Include leading $ sign

noprint

A column that is defined noprint will, not so strangly, not be printed at all.

DB_RECOVERY_FILE_DEST out ot space - PRCR-1079 / CRS-5017 / ORA-03113


Strange error on starting one of our databases today - why oracle can't just say the recovery file destination is 100% full I have no idea but anyway :


[oracle@ora01 ~]$ srvctl start database -db oradb
PRCR-1079 : Failed to start resource ora.cng.db
CRS-5017: The resource action "ora.oradb.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 129203
Session ID: 259 Serial number: 59108
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/product/12.2.0/diag/crs/ora01/crs/trace/ohasd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.oradb.db' on 'ora01' failed

From the alert log, trace files hit 100% on the recovery file dest size - more info from Mr Burleson.


http://www.dba-oracle.com/p_db_recovery_file_dest_size.htm

As this was RAC one node used srvctl to mount the database.

[oracle@ora01 ~]$ srvctl start database -db oradb -o mount

[oracle@ora01 ~]$ . oraenv


ORACLE_SID = [] ? oradb


The Oracle base has been changed from /u01/app/oracle/product/12.2.0 to /u01/app/oracle
[

oracle@ora01 ~]$ sqlplus / as sysdba

SQL> show parameter db_recovery_file_dest_size;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 8192M


SQL> alter system set db_recovery_file_dest_size = 16192M;

System altered.

SQL> alter database open;
Database altered.

Script from the Burleson website :

SQL>
col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999

select
name,
   ceil( space_limit / 1024 / 1024) size_mb,
   ceil( space_used / 1024 / 1024) used_mb,
   decode( nvl( space_used, 0),0, 0,
   ceil ( ( space_used / space_limit) * 100) ) pct_used
from
    v$recovery_file_dest
order by
   name desc;



NAME                                  SIZE_MB      USED_MB PCT_USED
-------------------------------- ------------ ------------ --------
+DATA                                  16,192        7,729       48


Just need to ensure the archive logs are cleared down so this does not happen again.

https://www.durham.computer/2019/04/a-very-quick-script-to-clear-down-rman.html


**NB**

Unless the "Archive Destination" is set to use the db_recovery_file_dest the archiver will use +DATA until it it is exhausted and won't max out at 12G; however the recovery area will still be used by the control files etc (see below) and can still reach 100%


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     9750
Next log sequence to archive   9751
Current log sequence           9752


SQL> show parameter db_reco

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 12G



SQL> l
  1* SELECT * FROM V$RECOVERY_AREA_USAGE

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES  CON_ID
------    ------------ ------------------------- --------------- ----------
CONTROL FILE                           .13       0               1          0
REDO LOG                              4.91       0               3          0
ARCHIVED LOG                             0       0               0          0
BACKUP PIECE                           .09       0               1          0
IMAGE COPY                               0       0               0          0
FLASHBACK LOG                            0       0               0          0
FOREIGN ARCHIVED LOG                     0       0               0          0
AUXILIARY DATAFILE COPY                  0       0               0          0

8 rows selected.


SQL>/ (script above)

NAME                                  SIZE_MB      USED_MB PCT_USED
-------------------------------- ------------ ------------ --------
+DATA                                  12,288          632        6