format select output information in plsql-CodePudding

I want to check the information in a tablespace, and I am using this SQL query:

select tablespace_name "GITBRANCHES",
Initial_extent "initial_ext",
Next_extent "next_ext",
Min_extents "min_ext",
Max_extents "max_ext",
Pct_increase
From dba_tablespaces;

column sizes very big and there is a line between each row, and every line finishes in the line below, how can I print it in a more formatted manner?

I tried:

exec print_table('select tablespace_name "GITBRANCHES",Initial_extent "initial_ext",Next_extent "next_ext",Min_extents "min_ext",Max_extents "max_ext" Pct_increase From dba_tablespaces');

but I keep getting:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PRINT_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

CodePudding user response：

The way I understood it, especially this:

column sizes very big and there is a line between each row, and every line finishes in the line below

is as if you ran that command in SQL*Plus. Did you? If so, set some settings, e.g.

SQL> col gitbranches format a25
SQL> set linesize 200

and then try again.

SQL> select tablespace_name "GITBRANCHES",
  2  Initial_extent "initial_ext",
  3  Next_extent "next_ext",
  4  Min_extents "min_ext",
  5  Max_extents "max_ext",
  6  Pct_increase
  7  From dba_tablespaces;

GITBRANCHES               initial_ext   next_ext    min_ext    max_ext PCT_INCREASE
------------------------- ----------- ---------- ---------- ---------- ------------
SYSTEM                          65536                     1 2147483645
SYSAUX                          65536                     1 2147483645
UNDOTBS1                        65536                     1 2147483645
TEMP                          1048576    1048576          1                       0
USER_DATA                       65536                     1 2147483645

Looks better, doesn't it?

For more info, read on e.g. Formatting SQL*Plus reports, SET system variables and other chapters in that book (if you want to be up-to-date, now you know what to search for within your own database version's documentation).