Monday, July 21, 2014

New Tablespace size script

I found this on Toadworld:

There are lots of useful scripts there:

I've changed a couple of the column formats from the original.

REM LOCATION:   Object Management\Tablespaces and DataFiles\Reports
REM FUNCTION:   Generate a report of Tablespace Space Availability
REM             including autoextend related space availability.
REM PLATFORM:   non-specific
REM REQUIRES:   dba_tablespaces, dba_data_files
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM ******************** Knowledge Xpert for Oracle Administration ********************
SET lines 160 pages 66 feedback off
COLUMN tablespace_name        format a20             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       ) total_used_space,
       fragments.total_tbs_free_bytes total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
           / AUTOEXTEND.total_growth_tbs
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);

Wednesday, July 16, 2014

Using a Date Picker in APEX

Adding a Date Picker to a page

Gee, this took me ages to figure out. 
I wanted a date picker on a home page that could have a start date and an end date selected, and then open a page with the information for just between those dates. 
I messed around with format masks, application globalisation settings, to_date, to_char etc and it drove me mad. Every time I tried to open the page, it said "no data found".

I still don’t know if this is possible – passing a date from one page to another, so the solution is to not use the home page, but direct to a new page, then enter the dates there, then have a ‘go’ button that submits the page with the dates. Phew.

Create a new page, select a Report, and enter the query something like this
select distinct user_name,sum(billable_hours) from client_data
where entry_date between :P17_START_DATE and :P17_END_DATE
and client_name not like '%one%'
and client_name not like '%MGA%'
and client_name is not null
group by user_name
order by 2 desc

Next, create the 2 items - P17_START_DATE and P17_END_DATE as Date Picker Items:


You can let everything default and just click "Next", then "Create Item" .

If you try and run the page, you'll see this:

 You need to add a button - Call it something like 'Go', and all you need to do is select 'Submit" for the action:

Save this, run the page, enter some dates using the calendar icons:


Then click the "Go" button:

 You can change the "No Data Found" to something like "Pick a Start and End Date Using the Calendar Icons" to make it more intuitive.

On the Home page, just put a button called "Date Range" and have it direct to this page (17 in this case).