Systems Engineering and RDBMS

Archive for February 27th, 2007

Oracle’s Recycle Bin

Posted by decipherinfosys on February 27, 2007

Oracle introduced the concept of recycle bin in 10g. This provides a safety net for the end users since if an object gets dropped by mistake, Oracle automatically stores it in the recyclebin.  Just like in windows recyclebin (or Mac’s trash), this is essentially a virtual container where all the dropped objects reside.  Underneath beneath the covers, those objects still occupy the same space as when they were created.  The table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin.  They just get renamed with a prefix of BIN$$.  There are two recyclebin views: user_recyclebin and dba_recyclebin.  The synonym recyclebin points to the user_recyclebin.

Each user has their own recycle bin and here is how you can access it:

select * from recyclebin;

You can also see the contents of the bin by just doing:

show recyclebin

In order to remove the contents of the bin, you can purge it:

purge recyclebin;

And here is how you can disable/enabled recyclebin in 10g:




ALTER SYSTEM SET recyclebin=OFF scope=BOTH;
ALTER SYSTEM SET recyclebin=ON scope=BOTH;

In order to recover the table from the recyclebin, you can use the flashback command:

flashback table xyz to before drop;

It just renames the dropped object back to it’s original name.

Posted in Oracle | Leave a Comment »

Tablespace Space Usage Information in Oracle

Posted by decipherinfosys on February 27, 2007

Here is a handy SQL that you can use to get the space usage for your tablespaces:

rem  Script: db_space_info.sql
rem  Purpose: This script returns the tablespace space usage information

set linesize 120 pagesize 1000 feedback off echo off
set underline =

ttitle left –
“Tablespace Usage Report” skip 2

col tablespace_name     heading ‘Tablespace’      format a30 truncate
col total_maxspace_mb   heading ‘Megs|Max Size’   format 9G999G999
col total_allocspace_mb heading ‘Megs|Allocated’  format 9G999G999
col used_space_mb       heading ‘Megs|Used’       format 9G999G999D99
col free_space_mb       heading ‘Megs|Free Till Max’       like used_space_mb
col free_space_ext_mb       heading ‘Megs|Free Till Ext’       like used_space_mb
col pct_used            heading ‘%|Used’          format 999D99
col pct_free            heading ‘%|Free’          like pct_used

break on report
compute sum label “Total Size:” of total_maxspace_mb total_allocspace_mb used_space_mb –
free_space_mb (used_space_mb/total_maxspace_mb)*100 on report

select /*+ALL_ROWS */
(alloc.total_allocspace_mb – free.free_space_mb) used_space_mb,
free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb) free_space_mb,
free.free_space_mb free_space_ext_mb,
((alloc.total_allocspace_mb – free.free_space_mb)/alloc.total_maxspace_mb)*100 pct_used,
((free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb))/alloc.total_maxspace_mb)*100 pct_free
FROM (SELECT tablespace_name,
ROUND(SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END)/1048576) total_maxspace_mb,
ROUND(SUM(bytes)/1048576) total_allocspace_mb
FROM dba_data_files
WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
GROUP BY tablespace_name) alloc,
(SELECT tablespace_name,
SUM(bytes)/1048576 free_space_mb
FROM dba_free_space
WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
GROUP BY tablespace_name) free
WHERE alloc.tablespace_name = free.tablespace_name (+)
ORDER BY pct_used DESC
clear columns
clear breaks
clear computes
ttitle off

Posted in Oracle | Leave a Comment »

VMWare Releases Workstation 6.0 Beta

Posted by decipherinfosys on February 27, 2007

VMWare recently released the 6.0 version of its Workstation virtualization solution. VMWare Workstation is intended as a single-user product, and is essentially an application installed on top of a Windows operating system.

New in Workstation 6.0

The big news with this latest release is that Workstation 6.0 supports Windows Vista as the host operating system. Previous versions of Workstation provided support for Vista virtual machines, but had to run on either a Windows 2000/2003/XP host operating system. Other features include support for both 32-bit and 64-bit operating systems, dual-monitor capability, and support for USB 2.0 devices.

In addition to the above-mentioned features, Workstation 6.0 offers two other new features that will be of particular interest to developers:

Virtual Debugging– Workstation 6.0 now comes with virtual debugging capabilities, which integrates with development tools such as Visual Studio and Eclipse so that developers can deploy, test, and debug programs directly within the virtual machine.

VM Record/Play – This is an experimental feature offered in the 6.0 Beta release that allows users to record the activity and configuration changes within a virtual machine over a given period of time, which then allows you to accurately duplicate the operations and configuration of the virtual machine during the time of the recording. This will be an incredibly handy tool to help pinpoint when certain changes (good or bad) were made to the virtual machine.

VMWare Workstation 6.0 is available for download at: , also be sure to review the release notes and documentation of this interesting new release.

Posted in VMWare | Leave a Comment »

What is Microsoft Forefront?

Posted by decipherinfosys on February 27, 2007

Microsoft Forefront is a complete line of business security products that encompasses security for all aspects of an organization’s infrastructure. The goal of Forefront is to bundle security features into one comprehensive package that provides ease of use through central administration. Forefront can be integrated with existing third-party security solutions.

Most organizations today have security measures in place for their information technology infrastructure, including anti-virus, firewalls, intrusion detection, spam filters, etc. Unfortunately, administration of theses measures is often disbursed, and can even be the responsibility of completely different groups. So, this essentially means that an organization’s security is only as effective as how well these different groups interact. Ensuring that various security solutions are administered effectively can be a headache in even the smallest of organizations, and with Forefront, Microsoft hopes to ease this pain.

Forefront is divided up into 3 realms: Server Security, Client Security, and what Microsoft calls Edge Security. Server Security includes protection for Exchange and SharePoint, Client Security provides malware protection for laptops and desktop PCs, and Edge Security revolves around Microsoft’s Integrated Security and Acceleration (ISA) Server, which provides network security along with VPN capabilities for remote client access. The Forefront product line breaks down as follows:

  • Client Security
  • Forefront Security for Exchange Server
  • Forefront Security for SharePoint
  • Forefront Security for Office Communications Server
  • Internet Security and Acceleration (ISA) Server 2006
  • Intelligent Application Gateway(IAG) 2007

Client, Server, and Edge Security solutions can be trial downloaded separately directly from the Microsoft Forefront homepage:

Is a bundled security solution like Forefront too good to be true? Perhaps. Past experience has shown most administrators that separate, dedicated security solutions are much better at what they do than a solution that offers multiple security functionality. However, if one looks more closely at the Forefront solution, one realizes that it is comprised of many components that have previously been available as separate standalone solutions. Microsoft’s attempt to combine these components under one administrative umbrella would certainly be a welcome improvement.

Posted in Security, Windows | Leave a Comment »

Apple Delays iTV Release

Posted by decipherinfosys on February 27, 2007

Apple Computer’s Apple TV product, or iTV as it has been commonly called, will not ship until sometime in March. The device is a box that is hooked up to a television, and can download movies and TV shows to a Mac or PC to be played on other televisions. The unit also allows users to synch up any number of iTunes libraries located on different components (PCs, laptops, etc).

You can view all of the details of Apple TV on Apple’s website:

Posted in News, Technology | Leave a Comment »

Using getdate() in a UDF

Posted by decipherinfosys on February 27, 2007

You might have tried using the getdate() non-deterministic function within a UDF some time and would have gotten an error stating:

“Invalid use of ‘getdate’ within a function”

That is because getdate() is a non-deterministic function (functions that do not always return the same result from the same input) and because of that restriction in SQL Server, it cannot be used within a function.  There are ways to get around this limitation though.  One of the ways is to create a view and then use it, for example:

create view GETDATE_VIEW
select getdate() as CurrentDateTime

create function dbo.GETDATE_FUNCTION
@datetime datetime
returns bit
declare @rc bit
select @rc = case when @datetime < CurrentDateTime then 1 else 0 end

return @rc

select dbo.GETDATE_FUNCTION(getdate())


However, do note that this view hack can return un-expected results depending upon how you are using it.  Here are two UDFs, one using the view and the other one does not:

RETURNS datetime AS
RETURN (SELECT currentdatetime FROM getdate_view)
CREATE function UDF_2
(@dt datetime)
RETURNS datetime
–Now, let’s see what we get when we execute a query with both the UDFs

declare @dt datetime
set @dt = current_timestamp
select distinct dbo.UDF_1(), xtype from sysobjects
select distinct dbo.UDF_2(@dt), xtype from sysobjects

On my instance and the database that I was using, the first one returned 16 rows and the second one returned 10 rows.  This has to do with when the function gets evaluated and thus the view one can return different results for records and end up with more rows within the same statement execution since it is getting evaluated per record.

Another way of doing is by using an openquery():

create FUNCTION dbo.UDF_3
RETURNS datetime
DECLARE @dt datetime
SELECT @dt = getdate_data FROM OPENROWSET(‘SQLOLEDB’, ‘Server=.;Trusted_Connection=yes;’,’SELECT getdate() AS getdate_data’)

select distinct dbo.UDF_3(), xtype from sysobjects

This returned a lot more distinct values for the getdate() values.  So, your best bet would be to pass in a parameter after assigning it the getdate() value and then use it within the UDF or if possible, use a stored procedure instead of the UDF.

Posted in SQL Server | 2 Comments »

Object Owner Prefix

Posted by decipherinfosys on February 27, 2007

Recompilation of a stored procedure (or a statement in SQL 2005) can be one of the causes of slow down in performance of your application.  There are a lot of things to account for in the code to ensure that re-compilations are not an issue.  One of the things is to always use the object owner prefixes for the objects.  Example: When user Sue calls a stored procedure dbo.test and dbo.test executes a query:

select col_x from table_x

the query optimizer has to do a look-up to decide whether to render the information from Sue.table_x or dbo.table_x.  So, when another user say Joe calls the same stored procedure, the optimizer will need to re-compile the query plan to determine whether Joe wants Joe.table_x or dbo.table_x.  If however, your code states:

select col_x from dbo.table_x

then there is no ambiguity at all and the re-compilation is avoided.  It is one of the best practices to always use an object owner prefix for your tables, views, procedures, UDFs etc. when the call is being made.

Posted in SQL Server | Leave a Comment »

Decipher partners with CerkiTek

Posted by decipherinfosys on February 27, 2007

A press release was made yesterday on our partnership with another company that is based out of Atlanta. Here is the link. We believe that this offers a lot of growth opportunities for both the companies and are eagerly looking forward to working with CerkiTek. We will be doing a press release on our site by the end of this week.

Posted in Decipher News | Leave a Comment »