Home » SQL & PL/SQL » Client Tools » set pages based on select query
set pages based on select query [message #329691] Thu, 26 June 2008 04:27 Go to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey

i want to set pagesize based on the result of the following query

SQL> select count(tablespace_name) from dba_data_files;

COUNT(TABLESPACE_NAME)
----------------------
                     5


Is it possible to do so ???
Re: set pages based on select query [message #329704 is a reply to message #329691] Thu, 26 June 2008 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes,
col nbfiles new_value psz
select count(tablespace_name) nbfiles from dba_data_files;
set pagesize &psz

Regards
Michel

Re: set pages based on select query [message #329712 is a reply to message #329704] Thu, 26 June 2008 05:28 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey any chance of supressing the output of the NBFILES??

I do not want this to appear in my html report only the tablespace output

SQL> col nbfiles new_value psz
SQL> select count(tablespace_name) nbfiles from dba_data_files;

   NBFILES
----------
         5

SQL> set pagesize &psz
SQL>
SQL> SELECT
  2         TABLESPACE,
  3         DB_FILES,
  4         TB_SIZE,
  5         NVL(FREE_SPACE,0) TB_FREE,
  6         B.TB_SIZE - FREE.FREE_SPACE TB_USED,
  7         TOTAL.TOTAL_SPACE,
  8         (FREE_SPACE / TB_SIZE) * 100 "FREE%",
  9         ((B.TB_SIZE - FREE.FREE_SPACE) / TB_SIZE) * 100 "USED%",
 10         TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') "Date"
 11  FROM
 12         (SELECT   TABLESPACE_NAME TABLESPACE
 13          FROM     DBA_DATA_FILES
 14          WHERE    TABLESPACE_NAME NOT LIKE 'TEMP%'
 15          GROUP BY TABLESPACE_NAME) A,
 16         (SELECT   TABLESPACE_NAME,
 17                   SUM(BYTES) / (1024 * 1024 * 1024) TB_SIZE
 18          FROM     DBA_DATA_FILES
 19          GROUP BY TABLESPACE_NAME) B,
 20         (SELECT   SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES / (1024 * 1024 *
1024),
 21                                             BYTES / (1024 * 1024 * 1024)))
OTAL_SPACE,
 22                   TABLESPACE_NAME,
 23                   COUNT(* ) DB_FILES
 24          FROM     SYS.DBA_DATA_FILES
 25          GROUP BY TABLESPACE_NAME) TOTAL,
 26         (SELECT   TABLESPACE_NAME,
 27                   SUM(BYTES / (1024 * 1024 * 1024)) FREE_SPACE
 28          FROM     SYS.DBA_FREE_SPACE
 29          GROUP BY TABLESPACE_NAME) FREE
 30  WHERE  A.TABLESPACE = TOTAL.TABLESPACE_NAME
 31         AND TOTAL.TABLESPACE_NAME = FREE.TABLESPACE_NAME
 32         AND A.TABLESPACE = B.TABLESPACE_NAME
 33         AND FREE.TABLESPACE_NAME = A.TABLESPACE
 34         AND (FREE_SPACE / TOTAL_SPACE) * 100 <= 5;


Re: set pages based on select query [message #329719 is a reply to message #329712] Thu, 26 June 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set termout off/on

Regards
Michel
Re: set pages based on select query [message #329723 is a reply to message #329719] Thu, 26 June 2008 05:58 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
no luck with that too.

the first table still appears
Re: set pages based on select query [message #329739 is a reply to message #329723] Thu, 26 June 2008 07:06 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only if you type it interactively not if it is in a script.

Regards
Michel
Previous Topic: How to configure URL iSQL*Plus DBA on 10G window Platform
Next Topic: Oracle Migration WorkBench, MSAccess to Oracle 9i
Goto Forum:
  


Current Time: Sat Dec 03 13:55:57 CST 2016

Total time taken to generate the page: 0.09953 seconds