PL/SQL Global Temporary Tables (GTT)

Sometimes we need to store data temporarily in a table . From Oracle 8i onward, we can manage temporary tables can be delegated to the server by using Global Temporary Tables.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

  Other Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

Reference : http://www.oracle-base.com/articles/misc/temporary-tables.php

Connect to remote oracle database from Sql Developer / TOAD by Tunneling

Computer networks use a tunneling protocol when one network protocol (the delivery protocol) encapsulates a different payload protocol. By using tunneling one can (for example) carry a payload over an incompatible delivery-network, or provide a secure path through an untrusted network.
Tunneling typically contrasts with a layered protocol model such as those of OSI or TCP/IP. Typically, the delivery protocol operates at an equal or higher level in the model than does the payload protocol, or at the same level.
To understand a particular protocol stack, network engineers must understand both the payload and delivery protocol sets.
As an example of network layer over network layer, Generic Routing Encapsulation (GRE), a protocol running over IP (IP Protocol Number 47), often serves to carry IP packets, with RFC 1918 private addresses, over the Internet using delivery packets with public IP addresses. In this case, the delivery and payload protocols are compatible, but the payload addresses are incompatible with those of the delivery network.
In contrast, an IP payload might believe it sees a data link layer delivery when it is carried inside the Layer 2 Tunneling Protocol (L2TP), which appears to the payload mechanism as a protocol of the data link layer. L2TP, however, actually runs over the transport layer using User Datagram Protocol (UDP) over IP. The IP in the delivery protocol could run over any data-link protocol from IEEE 802.2 over IEEE 802.3 (i.e., standards-based Ethernet) to the Point-to-Point Protocol (PPP) over a dialup modem link.
Tunneling protocols may use data encryption to transport insecure payload protocols over a public network (such as the Internet), thereby providing VPN functionality. IPsec has an end-to-end Transport Mode, but can also operate in a tunneling mode through a trusted security gateway.


A secure shell (SSH) tunnel consists of an encrypted tunnel created through an SSH protocol connection. Users may set up SSH tunnels to transfer unencrypted traffic over a network through an encrypted channel. For example, Microsoft Windows machines can share files using the Server Message Block (SMB) protocol, a non-encrypted protocol. If one were to mount a Microsoft Windows file-system remotely through the Internet, someone snooping on the connection could see transferred files. To mount the Windows file-system securely, one can establish a SSH tunnel that routes all SMB traffic to the remote fileserver through an encrypted channel. Even though the SMB protocol itself contains no encryption, the encrypted SSH channel through which it travels offers security.

Reverse ssh tunnel

To set up an SSH tunnel, one configures an SSH client to forward a specified local port to a port on the remote machine. Once the SSH tunnel has been established, the user can connect to the specified local port to access the network service. The local port need not have the same port number as the remote port.
SSH tunnels provide a means to bypass firewalls that prohibit certain Internet services — so long as a site allows outgoing connections. For example, an organization may prohibit a user from accessing Internet web pages (port 80) directly without passing through the organization's proxy filter (which provides the organization with a means of monitoring and controlling what the user sees through the web). But users may not wish to have their web traffic monitored or blocked by the organization's proxy filter. If users can connect to an external SSH server, they can create a SSH tunnel to forward a given port on their local machine to port 80 on a remote web-server. To access the remote web-server, users would point their browser to the local port at http://localhost/.
Some SSH clients support dynamic port forwarding that allows the user to create a SOCKS 4/5 proxy. In this case users can configure their applications to use their local SOCKS proxy server. This gives more flexibility than creating a SSH tunnel to a single port as previously described. SOCKS can free the user from the limitations of connecting only to a predefined remote port and server. If an application doesn't support SOCKS, one can use a "socksifier" to redirect the application to the local SOCKS proxy server. Some "socksifiers", such as Proxycap, support SSH directly, thus avoiding the need for a SSH client.

Reference :  http://en.wikipedia.org/wiki/Tunneling_protocol

Practical Situation :
Suppose your machine IP is :  A and you have access to server B but not in server C . But ,  you can connect to server C from server B .

 Now , you want to connect your local sql developer to oracle server which is in C through B.

Download Putty (http://www.putty.org/)

open it and you will find something like :

Now , in read marked section put machine address or ip address of server B .

Now , take a look to left section of the window and select Tunnels , after that check the read box which will allow local port to connect to other host .In source port put any custom port and in Destination put remote machine / server ip or as example ip of machine C with oracle port , Example : 10.20.20.21:1521 and click add button .
 

Now, you are ready to open the session , click to session section and click to open :

now , a command window will open , now log in to server B with your user name and password , this will open the required session and tunnel for us as configured before,and you are ready to connect your SQL developer with remote oracle namely in server C .

Now, open sql server and put user name , password of remote oracle server .
In  hostname put : localhost
In port number : source port as given earlier
In SID : SID of remote oracle
  

 Finally click to connect . If you configure everything as above you will get connected to remote oracle server from your local machine . Good Luck .

DBMS Network ACL of HTTP call using UTL_HTTP in oracle

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. reference

Scripts :

begin
dbms_network_acl_admin.create_acl (
   acl          => 'networkacl.xml',
   description  => 'Allow Network Connectivity',
   principal    => 'PUBLIC',
   is_grant     => TRUE,
   privilege    => 'connect',
   start_date   => SYSTIMESTAMP,
   end_date     => NULL);

dbms_network_acl_admin.assign_acl (
   acl         => 'networkacl.xml',
   host        => '*',
   lower_port  => NULL,
   upper_port  => NULL);

commit;
end;