Showing posts with label Oracle Miracle. Show all posts
Showing posts with label Oracle Miracle. Show all posts

Tuesday, 6 October 2009

A check list for NRIS buying a house in India

A check list for NRIS buying a house in India

Investment from any source in the housing sector is an appreciated aspect in today's indian real estate scenario, from an industry perspective. Let it be Resident Indians, NRIs or even companies, constructing houses creates jobs for a lot of people. A back of the napkin calculation shows that for a 1000 sq feet house, 100 direct employment (architect, building engineer, masons, helpers, electricians, plumbers, painters, carpenters, etc) and over 1000 indirect employment (people working in cement plant, brick kilns, tiles kilns, electrical fittings companies, saw mills, steel plants, paint companies, etc) opportunities are created. Of course the duration of the employment will depend on a number of factors like nearness to a supply sources for material and labour, access to high tech equipment, architecture, etc.

But to construct a house is not all that easy. It is not without substance that a Tamil saying goes, "Veetai Katti Par, Kalyanathai Panni Par" (Basically the saying rates constructing a house and having a child's marriage done among the toughest).

To an already difficult task, the sheer distance and absence during construction become problem multipliers for the NRIs. There was an NRI based out of the USA who got a wonderful sales pitch from a builder. The salesman met the NRI at his office in the USA and arranged for all the documentation and also sent video clippings of the apartment at Bangalore. Convinced on the genuineness, the NRI transferred Rs.50 lakhs to the builder's account. The date for the house warming was fixed after one month. The NRI could not make it to the function due to a pressing office work and had asked his parents to do the poojas.

The parents got the shock of their life, when they landed at the apartment complex the day before the poojas. The complex had only one sample apartment finished (the one in the video). They were told by the Project Manager at the site that the poojas can be done at anytime but the apartment can be delivered only after "6 months".

Another NRI who was building the house himself using an experienced and well referenced engineer found that his house orientation has been shifted by 15 feet. This left him space on the wrong side of the house squashing his plans to build a small commercial complex in future. They now have space for parking 4 cars but none for building a rent worthy space !!!

A number of checks could have been used to be on the safer side in both the above cases:

Thankfully there are a number of professional builders who are a lot more trust worthy. So doing a bit of research on the track record of a builder can help.

For any real estate purchase it is preferable to make visits to the sites before buying them. This exercise is worth it not only because we are committing a large amount of money but also because reversing the decision proves costly as well. If the NRI is not able to make it, he can request a trusted friend or relative to opt for the site visit.

Going for a housing loan through a bank will ensure that the money is released in stages only. This keeps the money safe during the construction. Also, all the banks at their local branches, have their list of shortlisted builders for whose constructions loans are pre-approved. It is better to buy only these constructions, as the banks are quite stringent in their norms for pre-approval and shortlist only those builders who have a proven track record and those project, which comply to all legal norms.

Post the construction, the management of the asset is one of the major issues faced by NRIs. There is no easy solution for this. There are some society associations which support the owners of the buildings with services like maintenance and rent collection. There are again the "friendly neighbor hood real estate agents" who may some times double up as the maintenance manager too. Many times though the "friendly" turn into "greedy" after some time. There are a few professional real estate management firms in most metros, who are now expanding into the 2nd Tier cities too.

Whether the construction rate quoted is for Built-up area or Carpet area? Construction is generally quoted for built-up area and rental is quoted only for the carpet area. There can be a difference of 15 % to 20% between the two based on the type of construction. Today in apartments there is the concept of super built-up area which apart from the built-up area includes stair case, common passages, fire escape passage, etc. The super built-up area can be bloated by as much as 50% of the carpet area.

Robert Allen, the Real Estate Mogul suggests the 100 - 20 - 10 - 1 rule for any real estate purchase. The idea is to check out 100 properties in person; shortlist 20 of them for a deeper scrutiny; enter into negotiation with sellers for 10 of the properties and finally buy the ONE that is best suited.

Technically there should be a check for all the statutory approvals - town planning (Nobody wants a flyover at arms length from the balcony!), water supply and sewage disposal, safety approval from the local fire department, etc. It is always better to ask for the encumbrance certificate and the title deed from the builder to get a legal opinion from a lawyer.

Don't hesitate to ask. This is probably the most important point. Many times, for avoiding being thought of as less intelligent, we question less. For any investing and particularly for real estate the more the questions asked the better the investment. The genuineness of the promoter can be gauged by the patience, the promptness and depth of the answers. Answers like, "Don't worry about that, we will manage", without going into the specifics are danger signs.

Take time. Do not restrain yourself by limiting the time for checking the properties and decision making to the time that you are present in India. A 2 to 4 week holiday cannot be hoped to be converted into a real estate investment period. Start the process before you come here. In case you cannot decide before you leave, it is OK. A Power of Attorney to a parent or a relative can be used to decide on the actual purchase even after you leave the shores of India.
Source: ET

Thursday, 13 August 2009

How to calculate 95 Percentile of a set of values in oracle?


How to calculate 95 Percentile of a set of values in oracle?

Oracle provides functions to calculate percentile values in a set of ordered data.

Inverse Percentile Functions
Using the CUME_DIST function, you can find the cumulative distribution
(percentile) of a set of values. However, the inverse operation (finding what value
computes to a certain percentile) is neither easy to do nor efficiently computed. To
overcome this difficulty, the PERCENTILE_CONT and PERCENTILE_DISC functions
were introduced. These can be used both as window reporting functions as well as
normal aggregate functions.
These functions need a sort specification and a parameter that takes a percentile
value between 0 and 1. The sort specification is handled by using an ORDER BY
clause with one expression. When used as a normal aggregate function, it returns a
single value for each ordered set.
PERCENTILE_CONT, which is a continuous function computed by interpolation,
and PERCENTILE_DISC, which is a step function that assumes discrete values. Like
other aggregates, PERCENTILE_CONT and PERCENTILE_DISC operate on a group
of rows in a grouped query, but with the following differences:
_ They require a parameter between 0 and 1 (inclusive). A parameter specified
out of this range will result in error. This parameter should be specified as an
expression that evaluates to a constant.
_ They require a sort specification. This sort specification is an ORDER BY clause
with a single expression. Multiple expressions are not allowed.
Normal Aggregate Syntax
[PERCENTILE_CONT | PERCENTILE_DISC]( constant expression )
WITHIN GROUP ( ORDER BY single order by expression
[ASC|DESC] [NULLS FIRST| NULLS LAST])
Inverse Percentile Example Basis
We use the following query to return the 17 rows of data used in the examples of
this section:
SELECT cust_id, cust_credit_limit, CUME_DIST()
OVER (ORDER BY cust_credit_limit) AS CUME_DIST
FROM customers WHERE cust_city='Marshal';
CUST_ID CUST_CREDIT_LIMIT CUME_DIST
Inverse Percentile Functions
SQL for Analysis and Reporting 21-29
---------- ----------------- ----------
28344 1500 .173913043
8962 1500 .173913043
36651 1500 .173913043
32497 1500 .173913043
15192 3000 .347826087
102077 3000 .347826087
102343 3000 .347826087
8270 3000 .347826087
21380 5000 .52173913
13808 5000 .52173913
101784 5000 .52173913
30420 5000 .52173913
10346 7000 .652173913
31112 7000 .652173913
35266 7000 .652173913
3424 9000 .739130435
100977 9000 .739130435
103066 10000 .782608696
35225 11000 .956521739
14459 11000 .956521739
17268 11000 .956521739
100421 11000 .956521739
41496 15000 1
PERCENTILE_DISC(x) is computed by scanning up the CUME_DIST values in each
group till you find the first one greater than or equal to x, where x is the specified
percentile value. For the example query where PERCENTILE_DISC(0.5), the result
is 5,000, as the following illustrates:
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP
(ORDER BY cust_credit_limit) AS perc_disc, PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY cust_credit_limit) AS perc_cont
FROM customers WHERE cust_city='Marshal';
PERC_DISC PERC_CONT
--------- ---------
5000 5000
The result of PERCENTILE_CONT is computed by linear interpolation between rows
after ordering them. To compute PERCENTILE_CONT(x), we first compute the row
number = RN= (1+x*(n-1)), where n is the number of rows in the group and x is the
specified percentile value. The final result of the aggregate function is computed by
Inverse Percentile Functions
21-30 Oracle Database Data Warehousing Guide
linear interpolation between the values from rows at row numbers CRN =
CEIL(RN) and FRN = FLOOR(RN).
The final result will be: PERCENTILE_CONT(X) = if (CRN = FRN = RN), then
(value of expression from row at RN) else (CRN - RN) * (value of expression for row
at FRN) + (RN -FRN) * (value of expression for row at CRN).
Consider the previous example query, where we compute PERCENTILE_
CONT(0.5). Here n is 17. The row number RN = (1 + 0.5*(n-1))= 9 for both groups.
Putting this into the formula, (FRN=CRN=9), we return the value from row 9 as the
result.
Another example is, if you want to compute PERCENTILE_CONT(0.66). The
computed row number RN=(1 + 0.66*(n-1))= (1 + 0.66*16)= 11.67. PERCENTILE_
CONT(0.66) = (12-11.67)*(value of row 11)+(11.67-11)*(value of row 12). These results
are:
SELECT PERCENTILE_DISC(0.66) WITHIN GROUP
(ORDER BY cust_credit_limit) AS perc_disc, PERCENTILE_CONT(0.66) WITHIN GROUP
(ORDER BY cust_credit_limit) AS perc_cont
FROM customers WHERE cust_city='Marshal';
PERC_DISC PERC_CONT
---------- ----------
9000 8040
Inverse percentile aggregate functions can appear in the HAVING clause of a query
like other existing aggregate functions.
As Reporting Aggregates
You can also use the aggregate functions PERCENTILE_CONT, PERCENTILE_DISC
as reporting aggregate functions. When used as reporting aggregate functions, the
syntax is similar to those of other reporting aggregates.
[PERCENTILE_CONT | PERCENTILE_DISC](constant expression)
WITHIN GROUP ( ORDER BY single order by expression
[ASC|DESC] [NULLS FIRST| NULLS LAST])
OVER ( [PARTITION BY value expression [,...]] )
This query computes the same thing (median credit limit for customers in this result
set, but reports the result for every row in the result set, as shown in the following
output:
SELECT cust_id, cust_credit_limit, PERCENTILE_DISC(0.5) WITHIN GROUP
(ORDER BY cust_credit_limit) OVER () AS perc_disc,
Inverse Percentile Functions
SQL for Analysis and Reporting 21-31
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cust_credit_limit)
OVER () AS perc_cont
FROM customers WHERE cust_city='Marshal';
CUST_ID CUST_CREDIT_LIMIT PERC_DISC PERC_CONT
---------- ----------------- ---------- ----------
28344 1500 5000 5000
8962 1500 5000 5000
36651 1500 5000 5000
32497 1500 5000 5000
15192 3000 5000 5000
102077 3000 5000 5000
102343 3000 5000 5000
8270 3000 5000 5000
21380 5000 5000 5000
13808 5000 5000 5000
101784 5000 5000 5000
30420 5000 5000 5000
10346 7000 5000 5000
31112 7000 5000 5000
35266 7000 5000 5000
3424 9000 5000 5000
100977 9000 5000 5000
103066 10000 5000 5000
35225 11000 5000 5000
14459 11000 5000 5000
17268 11000 5000 5000
100421 11000 5000 5000
41496 15000 5000 5000
Inverse Percentile Restrictions
For PERCENTILE_DISC, the expression in the ORDER BY clause can be of any data
type that you can sort (numeric, string, date, and so on). However, the expression in
the ORDER BY clause must be a numeric or datetime type (including intervals)
because linear interpolation is used to evaluate PERCENTILE_CONT. If the
expression is of type DATE, the interpolated result is rounded to the smallest unit
for the type. For a DATE type, the interpolated value will be rounded to the nearest
second, for interval types to the nearest second (INTERVAL DAY TO SECOND) or to
the month(INTERVAL YEAR TO MONTH).
Like other aggregates, the inverse percentile functions ignore NULLs in evaluating
the result. For example, when you want to find the median value in a set, Oracle
Database ignores the NULLs and finds the median among the non-null values. You
Hypothetical Rank and Distribution Functions
21-32 Oracle Database Data Warehousing Guide
can use the NULLS FIRST/NULLS LAST option in the ORDER BY clause, but they
will be ignored as NULLs are ignored.

Reference: Oracle Datawarehousing Guide

Monday, 13 July 2009

Adding a New Node to an Oracle RAC Cluster


Contents

1.        Introduction:         PAGEREF _Toc190769674 \h 2

2.        Preparing Access to the New Node         PAGEREF _Toc190769675 \h 2
2.1        Create the operating system user and group on the new node         PAGEREF _Toc190769676 \h 2
2.2        Configuring the Secure Shell         PAGEREF _Toc190769677 \h 2

3.        Clone the Oracle Clusterware Home Directory         PAGEREF _Toc190769678 \h 5

4.        Clone the Automatic Storage Management Home Directory         PAGEREF _Toc190769679 \h 6

5.        Clone Oracle Software Home Directory         PAGEREF _Toc190769680 \h 6

6.        Creating a Listener on the New Node         PAGEREF _Toc190769681 \h 7

7.        Create a new cluster instance on the new node         PAGEREF _Toc190769682 \h 8

8.        Conclusions         PAGEREF _Toc190769683 \h 9



 

1.        Introduction:
This process describes how to add a new node to an existing Oracle Real Application Clusters (Oracle RAC) environment.

2.        Preparing Access to the New Node
The following steps needs be followed to prepare the node before it is added to the cluster:

2.1        Create the operating system user and group on the new node
When installing Oracle RAC on UNIX and Linux platforms, the software is installed on one node, and OUI uses the Secure Shell (SSH) to copy the software binary files to the other cluster nodes.

a)        If this is the first time Oracle software is being installed on the new node and the dba group does not exist, then create the dba group as follows:



-        Login as root user
-        # /usr/sbin/groupadd dba


b)        If the user that owns the Oracle software does not exist on the new node then create the user as follows:


# useradd -u <UID> –g dba -d <Home>  -r oracle

Set the password for the oracle account using the following command. The password should be same as the other nodes.

# passwd oracle

Note: The UID should be same as the UID of oracle from any existing node.

c) Verify that the attributes of the user oracle are identical on all the existing nodes:

# id oracle

2.2        Configuring the Secure Shell
a)        Log in to the new node as the oracle user
b)        Determine if a .ssh directory exists in the oracle user's home directory. If not, create the .ssh directory and set the directory permission so that only the oracle user has access to the directory, as shown here:


$ ls -a $HOME
$ mkdir ~/.ssh
$ chmod 700 ~/.ssh








c)        Create the RSA-type public and private encryption keys on the new node as follows:


$/usr/bin/ssh-keygen -t rsa

At the prompts:

        Accept the default location for the key file by pressing the Enter key.
        When prompted for a pass phrase, enter and confirm a pass phrase that is different from the oracle user's password.


This command creates the public key in the /home/oracle/.ssh/id_rsa.pub file and the private key in the /home/oracle/.ssh/id_rsa file.

d)        Create the DSA type public and private keys on the new node as follows:


$ /usr/bin/ssh-keygen -t dsa

At the prompts:

        Accept the default location for the key file by pressing the Enter key.
        When prompted for a pass phrase, enter and confirm a pass phrase that is different from the oracle user's password.


This command creates the public key in the /home/oracle/.ssh/id_dsa.pub file and the private key in the /home/oracle/.ssh/id_dsa file.

e)        Add the Keys to an Authorized Key File


Use Secure Copy (SCP) or Secure FTP (SFTP) to copy the authorized_keys file to the oracle user .ssh directory from any existing cluster node. The following example uses SCP to copy the authorized_keys file to the new node from an existing node.

        Log on to an existing node as oracle user


        $scp ~/.ssh/authorized_keys <New Node>:<oracle user HOME>/.ssh/


You are prompted to accept an RSA or DSA key. Enter yes, and you see that the node you are copying to is added to the known_hosts file.

When prompted, provide the password for the oracle user, which should be the same on all the nodes in the cluster (Note: this is the user password, not the newly specified passphrase). The authorized_keys file is then copied to the remote node.

        Log on to the new node as oracle user where you copied the authorized_keys file. Then change to the .ssh directory, and using the cat command, add the RSA and DSA keys for the new node to authorized_keys file as follows:


$ cat id_rsa.pub  >> authorized_keys
$ cat id_dsa.pub  >> authorized_keys

        Use SCP to copy the authorized_keys file from the new node to all the other existing cluster nodes, overwriting the existing version.


$scp ~/.ssh/authorized_keys <Existing Node>:<oracle user HOME>/.ssh/

        From the new node complete the SSH configuration by using the ssh command to retrieve the date on each node in the cluster.


$ ssh <Existing Node> date

The first time you use SSH to connect to one node from another node, you see a message similar to the following:

The authenticity of host 'docrac1(143.46.43.100) can't be established. RSA key fingerprint is 7z:ez:e7:f6:f4:f2:d1:a6:f7:4e:zz:me:a7:48:ae:f6:7e. Are you sure you want to continue connecting (yes/no)? yes
Enter yes at the prompt to continue. You should not see this message again when you connect to this node to the other node. If you see any other messages or text, apart from the date, then the installation can fail.

        Add the public and private node names for the new node to the /etc/hosts file on the existing nodes


        Verify that the new node can be accessed (using the ping command) from the existing nodes


        Run the following command on any existing node to verify that the new node has been properly configured:


$ cluvfy stage -pre crsinst -n <New Node Name>

3.        Clone the Oracle Clusterware Home Directory
Use Oracle Universal Installer (OUI) to add an Oracle Clusterware home to the new node being added to the Oracle RAC cluster.

        Go to the <Cluster Home>/oui/bin directory of an existing node and run the addNode.sh script.



$ cd <Cluster Home>/oui/bin
$ ./addNode.sh

        OUI starts and first displays the Welcome window.


Click Next.

The Specify Cluster Nodes to Add to Installation window appears.

        Select the new node or nodes that you want to add. click Next.
        Verify the entries that OUI displays on the Summary Page and click Next.
        Run the rootaddNode.sh script from the <Cluster Home>/install/ directory on the existing node when prompted to do so.


Basically, this script adds the node applications of the new node to the OCR configuration.
        Run the orainstRoot.sh script on the new node which is being added if OUI prompts you to do so.
        Run the <Cluster Home>/root.sh script on the new node to start Oracle Clusterware on the new node.


        Add the new node's Oracle Notification Services (ONS) configuration information to the shared Oracle Cluster Registry (OCR).


-        Obtain the ONS port identifier used by the new node, by running the following command from the <Cluster Home>/opmn/conf directory on an existing node node:


$cat ons.config

-        After you locate the ONS port number for the new node, you must make sure that the ONS on existing nodes can communicate with the ONS on the new node.


From the <Cluster Home>/bin directory on an existing node, run the Oracle Notification Services configuration utility as shown below, where remote_port is the port number obtained from previous step:

$ ./racgons add_config <New Node>:remote_port

        At the end of the cloning process, you should have Oracle Clusterware running on the new node. To verify the installation of Oracle Clusterware on the new node, you can run the following command as the root user on the newly configured node:


$CRS_home/bin/cluvfy stage -post crsinst -n docrac3 –verbose

4.        Clone the Automatic Storage Management Home Directory
Use Oracle Universal Installer (OUI) to add ASM home to the new node being added to the Oracle RAC cluster.

        Go to the $ASM_HOME/oui/bin directory on an existing node and run the addNode.sh script.



        When OUI displays the Node Selection window, select the new node to be added then click Next.


        Verify the entries that OUI displays on the Summary window, then click Next.


        Run the root.sh script on the new node, from the ASM home directory on that node when OUI prompts you to do so.


You now have a copy of the ASM software on the new node.

5.        Clone Oracle Software Home Directory
Use Oracle Universal Installer (OUI) to add Oracle Software home to the new node being added to the Oracle RAC cluster.

        Go to the $ORACLE_HOME/oui/bin directory on an existing node and run the addNode.sh script.



        When OUI displays the Specify Cluster Nodes to Add to Installation window, select the node to be added, then click Next.


        Verify the entries that OUI displays in the Cluster Node Addition Summary window, then click Next.


        Run the root.sh script on the new node, from the $ORACLE_HOME directory on that node when OUI prompts you to do so.


After completing these steps, you should have an installed Oracle RAC home on the new node.

6.        Creating a Listener on the New Node
To service database instance connection requests on the new node, you must create a Listener on that node. Use the Oracle Net Configuration Assistant (NETCA) to create a Listener on the new node. Before beginning this procedure, ensure that your existing nodes have the $ORACLE_HOME environment variable set correctly.

        Start the Oracle Net Configuration Assistant by entering netca at the system prompt from the $ORACLE_HOME/bin directory.



        Select Listener configuration, and click Next.


        NETCA displays the Listener Configuration, Listener window.


        Select Add to create a new Listener, then click Next.


        NETCA displays the Listener Configuration, Listener Name window.


        Accept the default value of LISTENER for the Listener name by clicking Next.


        NETCA displays the Listener Configuration, Select Protocols window.


        Choose TCP and move it to the Selected Protocols area, then click Next.


        NETCA displays the Listener Configuration, TCP/IP Protocol window.


        Choose Use the standard port number of 1521, then click Next.


        NETCA displays the Real Application Clusters window.


        Select Cluster configuration for the type of configuration to perform, then click Next.


        NETCA displays the Real Application Clusters, Active Nodes window.


        Select the name of the node you are adding, then click Next.


        NETCA creates a Listener using the configuration information provided. You can now exit NETCA.



You should now have a Listener named LISTENER running on the new node.

7.        Create a new cluster instance on the new node
        Start DBCA by entering dbca at the system prompt from the $ORACLE_HOME/bin directory.



        Select Oracle Real Application Clusters database, and then click Next.


DBCA displays the Operations window.

        Select Instance Management, and then click Next.


DBCA displays the Instance Management window.

        Select Add an Instance, then click Next.


DBCA displays the List of Cluster Databases window, which shows the databases and their current status, such as ACTIVE or INACTIVE.

        In the List of Cluster Databases window, select the active Oracle RAC database to which you want to add an instance. Enter the user name and password for the database user that has SYSDBA privileges. Click Next.


DBCA will spend a few minutes performing tasks in the background, then it will display the Instance naming and node selection window.

        In the Instance naming and node selection window, enter the instance name in the field at the top of this window if the default instance name provided by DBCA does not match your existing instance naming scheme.


Click Next to accept the instance name

DBCA displays the Instance Storage window.

        In the Instance Storage window, you have the option of changing the default storage options and file locations for the new database instance. In this example, you accept all the default values and click Finish.


DBCA displays the Summary window.

        Review the information in the Summary window, then click OK to start the database instance addition operation. DBCA displays a progress dialog box showing DBCA performing the instance addition operation.


        During the instance addition operation, if you are using ASM for your cluster database storage, DBCA detects the need for a new ASM instance on the new node.


When DBCA displays a dialog box, asking if you want to ASM to be extended, click Yes.

After DBCA extends ASM on the new node and completes the instance addition operation, DBCA displays a dialog box asking whether or not you want to perform another operation. Click No to exit DBCA.

8.        Conclusions
You should now have a new cluster database instance and ASM instance running on the new node. After you terminate your DBCA session, you should run the following command to verify the administrative privileges on the new node and obtain detailed information about these privileges:

$ <Cluster Home>/bin/cluvfy comp admprv -o db_config -d oracle_home -n <New Node Name> -verbose

Converting date from local time zone to GMT, BST


I just thought I should add some technical bits to my blog that could be helpful to people trying to find some quick help. Here is one.

--*1*--
Oracle functions to transpose time between different time zones to GMT.

Oracle provides a number of functions to transpose between the different time zones to GMT. You need and need to store 'Olson' code to convert from the required time zone.

Please note when using these functions if the country area is used e.g. 'Europe\London' then this will also convert to BST (if we have entered day light savings). However if the 'GMT' code is used then this will always be set to GMT.

SELECT
-- Show timestamp as if in 'Europe/London'
CAST ('01-JAN-2009 09:00:01.01' AS TIMESTAMP) at time zone 'Europe/London' as A,
-- Show timestamp as if in 'Turkey'
CAST ('01-JAN-2009 09:00:01.01' AS TIMESTAMP) at time zone 'Turkey' as B,
-- Take timestamp from 'Europe/London' and show as 'Europe/London'
FROM_TZ(CAST('01-JAN-2009 09:00:01.01' AS TIMESTAMP), 'Europe/London' )  AT TIME ZONE 'Europe/London' as C,
-- Take timestamp from 'Turkey' and show as 'Europe/London'
FROM_TZ(CAST('01-JAN-2009 09:00:01.01' AS TIMESTAMP), 'Turkey' )  AT TIME ZONE 'Europe/London' as D,
-- Take timestamp from 'Europe/London' and show as 'Turkey' remove Timezone
CAST (FROM_TZ(CAST ('01-JAN-2009 09:00:01.01' AS TIMESTAMP), 'Europe/London' )  AT TIME ZONE 'Turkey' AS TIMESTAMP) as E,
-- Take timestamp from 'Turkey'
FROM_TZ(CAST('01-JAN-2009 09:00:01.01' AS TIMESTAMP), 'Turkey' ) as F,
-- Show offset for 'EST'
tz_offset('EST') as G,
-- Show offset for 'Turkey'
tz_offset('Turkey') as H
FROM
DUAL

A list of the time zone names can be retrieved from the Oracle view V$TIMEZONE_NAMES

--***--