Oracle Query to get table size across schemas

I googled for this and got this from some website. Do not remember the website from where I got this query. Thanks for the original author. Pasting the query for my personal records:

SELECT owner,
       table_name,
       trunc(SUM(bytes) / 1024 / 1024) meg
FROM   (SELECT segment_name table_name,
               owner,
               bytes
        FROM   dba_segments
        WHERE  segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name,
               i.owner,
               s.bytes
        FROM   dba_indexes  i,
               dba_segments s
        WHERE  s.segment_name = i.index_name
        AND    s.owner = i.owner
        AND    s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.segment_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.index_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBINDEX')
WHERE  owner NOT IN ('SYS', 'SYSTEM')
GROUP  BY table_name,
          owner
HAVING SUM(bytes) / 1024 / 1024 > 30 /* Ignore tables lower than 30 MB */
ORDER  BY SUM(bytes) DESC

jQuery tabs within Oracle APEX

This tip was posted in Oracle Apex Knowledge Group by Richard Martens. Its my sincere pleasure in thanking the author for this simple tip.

The original blog is available here. I am posting the steps first, and a slight deviation which makes the setup one time only through this article.

The idea is to create a region first and all sub-regions as tabs. If this design suits your application then its simple as copy-paste.Create a template

1. Go to Shared Components 
2. Navigate to Templates
3. Press the Create Button
4. Choose Region
5. Choose From Scratch
6. Provide the name jQuery Tabs
7. Choose Custom 1 for template class
8. Press Create button
9. Once the template is created, find the jQuery tabs from the list and click to edit.
10. Paste both the following codes in Definition Section:

1. <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>
   #BODY##SUB_REGION_HEADERS##SUB_REGIONS#
   <div style="clear:both;"></div>
   </div>
   <link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.tabs.css" type="text/css" />
   <script src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.tabs.min.js" type="text/javascript"></script>

2. <script type="text/javascript">
    apex.jQuery(function() {
    apex.jQuery("##REGION_STATIC_ID#").tabs();
    });
   </script>

11. Paste the following in Sub Regions > Header Template

1. <ul style="height: auto;">#ENTRIES#</ul>

12. Paste the following in Sub Regions > Header Entry Template

1. <li><a style="overflow:auto;" href="##REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION_TITLE#</a></li>

13. Paste the following in Sub Regions > Template

1. <div id="#REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION#</div>

The template is now ready. Now you are ready to create regions in your page. While creating the Parent region, choose the template as jQuery Tabs.

If you are wondering what is the improvisation I have done, its present in Step 10.2

In Richard Martens blog the step 10.2 is mentioned to be done for every page. I have modified it to be included in the page template itself. A very small and negligible improvisation. 

Oracle APEX - Remove Show All from region selector

Oracle Application Express (APEX) offers a lot of regions to be displayed in a single page without cluttering the screen display. One such way of making a page to display lot of contents is using the Region Display Selector.

In this tip however I am not going to show how to display the "Region Display Selector" but an inherent problem with this control.

I have a screenshot of this control in action. I have Five regions in this sample page.

For each region this "Display Selector creates a link in its control. As soon as you click a particular region, only that specific region will be displayed. The remaining will be hidden.

The "drawback" is that there is no option to hide the first option "Show All". Initially when the page loads the "Show All" will be selected and all regions will be displayed.

I think this should be a great feature which should be available in-built within Apex. But as of 4.2.3 version which I am using, this feature is not available. But its not a big deal to make it hidden. As mentioned in this blog, it only requires 3 lines of jQuery code to be added. I am making this post as a reference to myself based on the blog entry mentioned.

Do the following to make this change in your page:
Step 1: Edit Page and create a Dynamic Action

Step 2: Select the Page Load event, and Execute Javascript code while creating the dynamic action.

The magical 3 line of jQuery Code is below:

$('.apex-rds li:first-child').remove();

$('.apex-rds li:first-child').addClass('apex-rds-first');

$('.apex-rds li:first-child > a').trigger('click');

That is all required to be done for making the change. The Show All will be hidden and only the first region will be made visible.

Thanks to Nick Buytaert for this tweak.