Synonyms in Oracle.

Synonyms are aliases for objects in database. Synonyms make it easy for a schema to access objects in other schema. Normally objects in other schema can be accessed by prefixing schema name to the object, but with the use of synonym there is no need to use schema name while accessing the object.

Synonyms can be public as well as private.

Public Synonym : Public synonyms are objects of public schema and can be accessed by any schema in the same or different database(assuming databases are connected by a link).

Let's see how to create a public synonym.

CREATE PUBLIC SYNONYM tab1 FOR sch1.tab1;

Now the table tab1 can be accessed by any schema using name tab1.

Private Synonym : Private synonyms are private to a schema. They can be referenced only by schema which owns the object. Private synonym can be created by removing the private keyword from the above CREATE statement.

CREATE SYNONYM tab1 FOR sch1.tab1;

Note :

1) Knowing a public synonym for an object doesn't mean that a schema can access the object as it wants. Access policies to the object will always be determined by access permissions granted to the schema for the object.

2) Permission for creation of a public synonym has to be granted by DBA.

For more info visit.

Pin objects in Oracle shared pool using DBMS_SHARED_POOL package.

DBMS_SHARED_POOL package can be used to pin PL/SQL objects, SQL cursors, triggers, sequence inside shared pool area in Oracle database memory.

Oracle has shared pool inside System Global Area(SGA) where it caches compiled PL/SQL and SQL code to improve response and reduce execution time when the same code is executed again. Whenever we execute a SQL or PL/SQL code then Oracle checks for  compiled version of the code in the shared pool area. If it finds the compiled version then the code is executed from the shared pool area, this is known as soft parse, or else, code is fetched from disk, compiled, executed and stored in the shared pool area for future reference.

Shared pool area has a memory limit, so Oracle keeps moving out objects as new objects come in, i.e. aging process is followed to move older objects out of the memory to make space for new objects. Sometimes, for improving performance, there is a need to keep some objects in the shared pool area and skip the usual aging process irrespective of the new objects coming in, so we pin those objects permanently inside the shared pool area. These objects will then not be removed by usual aging process unless we explicitly ask Oracle to unpin them.

Let's see how to use this package.

This package consists of some procedures which can be called to pin/unpin the objects.

PROCEDURE DBMS_SHARED_POOL.KEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

KEEP procedure can be used to pin object inside shared pool area.
name: Name of the object to be pinned.
flag: Type of the object to be pinned.

Value of flag can be
P : Procedure/Function/Package
T : Type
Q : Sequence
C : Cursor
R : Trigger

PROCEDURE DBMS_SHARED_POOL.UNKEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

UNKEEP procedure can be used to unpin object from shared pool area. Object will follow normal aging process once unpinned.
name: Name of the object to be unpinned.
flag: Type of the object to be unpinned.

PROCEDURE DBMS_SHARED_POOL.SIZES
(minsize IN NUMBER);

SIZES procedure can be used to get information about all objects which are currently in shared pool area and exceed the size specified in parameter of the procedure.

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
(threshold_size IN NUMBER);

ABORTED_REQUEST_THRESHOLD procedure can be used to avoid Oracle flushing old objects in order to make space for a big new object. When threshold is set then any object greater than threshold size will not be allowed in memory if sufficient memory space is not available in shared pool.

NOTE: DBMS_SHARED_POOL is not accessible by a normal user, also there is no public synonym referencing the package. This package is mostly used by the Oracle DBAs.

For more info visit

Use copy command to copy tables on local or remote database.

Generally we use CREATE TABLE tab1 AS SELECT..... to copy a table's data to some other table.

There are some limitations with this method.

1) Oracle doesn't commit in between while copying data, so undo segment may run out of space.

2) This technique cannot be used to copy LONG type columns in a table.

Let's see how to use copy command to get rid of these problems.

Suppose we want to create a new table tab_new with data of table tab_old. In this case copy command will be used as

COPY FROM username/password@db_name
CREATE tab_new
USING SELECT * FROM tab_old;

This will create a new table tab_new with data of table tab_old.

Some other options which can be used in place of CREATE are

INSERT : Inserts records in an existing table.

APPEND : Creates the target table, if it doesn't exists, and inserts records.

REPLACE : Drops the existing table, creates new table and inserts records.

For more info visit.

How to configure SSL(Secure Sockets Layer) in Apache?

SSL (Secure Sockets Layer) is a protocol which is used for communicating securely over the network. SSL provides both, encryption as well as authentication. For example, in client-server communication it encrypts the data, which browser(client) sends to the server or server sends to the client(browser), as well as authenticates the server to the client. Here authentication means that the client is confirmed that the server to which it is talking is genuine.

SSL works on public/private key cryptography. In SSL, certificates are used to prove the identity to other user.

What is a certificate and how does it work?

Let's say there are three parties. Party 1 is client which uses the services of Party 2. Party2 proves its identity by providing a certificate, signed by a third party, to party 1. Both the parties, party 1 as well as party 2, trust party 3. A certificate contains some information about the party and its public key, it is signed by a CA(Certificate Authority). A certificate authority is the third party which is trusted by both, owner of the certificate as well as the party relying on the certificate. Most of the popular browsers contain information about all the CAs, i.e. they know the genuine CAs, and so these browsers trust the certificates signed by them. If the certificate is signed by a party which is not trusted by the browser then it gives warning that the certificate owner may not be the one who it claims to be. In this case although your channel is secure but the other party is not authenticated, so you might be giving your secret information to a scrupulous person over an encrypted channel which is as good as having no security.

Working:

1) When SSL is configured on server(apache), the client(browser) is presented with the server certificate signed by a known CA. Now Client knows the public key of the server.

2) For authentication, server creates a message and computes its hash value. Server encrypts the hash with its private key. Server sends both, message as well as its encrypted hash, to the client.

3) Client decrypts the encrypted hash using public key of the server. It computes hash value of the message it gets from the server. It compares both the hashes to check the authenticity of the server. If both the hashes are same then the client is talking to a genuine server.

4) As authentication is complete, both client and server need some way to pass the data securely. To achieve this, client selects a synchronous key, encrypts it with the server's public key and sends the encrypted key to the server.

5) Server, after getting the encrypted synchronous key, decrypts it. Now both, client and server, have the synchronous key. They encrypt the data to be passed over the network using this synchronous key. So, security is also achieved in this step.

 Let's see how to configure SSL in apache.

We assume that apache is installed with SSL module(mod_ssl), and open SSL is also installed on the machine.

Make apache listen for HTTPS requests on port 443 by executing the following command.

a2enmod ssl

The above command enables SSL module of apache

Restart apache

/etc/init.d/apache2 restart

Apache has started listening on port 443.

Use the following command to generate a certificate request and a private key.

openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

It will ask for some basic information which you have to answer to get the certificate request generated.

Once your private key and certificate request is generated, you will have to get it signed. You can get your certificate signed by a commercial CA or you can sign it yourself. The only problem in signing it yourself is that the browser will give warning since it does not know the certificate signing authority.

Let's see how to self sign a certificate request. Use the following command to sign

openssl x509 -req -days 100 -in server.csr -signkey server.key -out server.crt

The above command creates a certificate which is valid for 100 days. Now you have got two files generated.

server.key private key

server.crt certificate

Let's configure apache to use these.

Supposing that there is virtual hosting on this server.

Inside directory /etc/apache2/sites-enabled / create a copy of default-ssl and rename it. Let's say the new file is abc-ssl

cat default-ssl > abc-ssl

Now we have to tweak at some places in abc-ssl

<VirtualHost <Your-IP-Address>>

ServerName <your-site-name>

DocumentRoot <site-directory-path>

<Directory <site-directory-path>>

SSLCertificateFile <File-path-certificate>

SSLCertificateKeyFile <File-path-key>

With all these changes done, run the following command to enable the ssl configurarion for this website

a2ensite abc-ssl

Reload apache with changed configuration

/etc/init.d/apache2 reload

Now the server will listen and forward any https request for this site.

You can also tweak .htaccess to configure whether you want to access whole website or only some of the pages of the website with https.

For more info visit.

Dynamic queries with an unknown number of inputs...

Sometimes there is a need to pass dynamic number of inputs to a query, based on conditions satisfied or data from some other source/query, as the program executes. Here, we will see how to pass dynamic/unknown number of inputs to a query.

Let's see how to do it. We assume a situation where we get inputs, to be passed to a dynamic query, from a different select query.  

DECLARE CURSOR c_fetch_ids IS 

SELECT ids FROM employee WHERE name LIKE 'John %'; 

v_counter NUMBER; 

d NUMBER; 

v_dyn_stmt VARCHAR2(1000); 

v_cur NUMBER; 

v_temp employee.ids%TYPE; 

v_tab_ids DBMS_SQL.NUMBER_TABLE; 

bind_names DBMS_SQL.VARCHAR2_TABLE; 

v_names DBMS_SQL.VARCHAR2_TABLE; 

v_salaries DBMS_SQL.NUMBER_TABLE; 

BEGIN v_counter := 0; 

OPEN c_fetch_ids; 

LOOP FETCH c_fetch_ids INTO v_temp; 

EXIT WHEN c_fetch_ids%NOTFOUND; 

v_counter := v_counter+1; 

v_tab_ids(v_counter) := v_temp; 

END LOOP; CLOSE c_fetch_ids; 

v_dyn_stmt := 'SELECT name, salary FROM employee WHERE ids IN ('; 

FOR v_counter IN 1 .. v_tab_ids.count 

LOOP 

bind_names(v_counter) := v_counter; 

IF v_counter=1 

THEN 

v_dyn_stmt := v_dyn_stmt||' :1'; 

ELSE v_dyn_stmt := v_dyn_stmt||' ,:'||v_counter; 

END IF; 

END LOOP; 

v_dyn_stmt := v_dyn_stmt||')'; 

v_cur := DBMS_SQL.OPEN_CURSOR; 

DBMS_SQL.PARSE(v_cur, v_dyn_stmt, DBMS_SQL.NATIVE); 

for v_counter IN 1 .. v_tab_ids.count 

LOOP 

DBMS_SQL.BIND_VARIABLE(v_cur,bind_names(v_counter),v_tab_ids(v_counter)); 

END LOOP; 

DBMS_SQL.DEFINE_ARRAY(v_cur, 1, v_names, 10, 1); 

DBMS_SQL.DEFINE_ARRAY(v_cur, 2, v_salaries, 10, 1); 

d := DBMS_SQL.EXECUTE(v_cur); 

LOOP 

d := DBMS_SQL.FETCH_ROWS(v_cur); 

DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_names); 

DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_salaries); 

EXIT WHEN d!=10; 

END LOOP; 

DBMS_SQL.CLOSE_CURSOR(v_cur); 

END;

The above block fetches names and salaries of employees, 10 at a time, after taking dynamic number of inputs(ids) which come from a different query with the condition that name should be starting as 'John %'.

For more info visit

Execute dynamic queries using Native Dynamic SQL(NDS) in PL/SQL.

Dynamic SQL statements are a powerful way to execute dynamic queries in PL/SQL. In this approach, queries are designed as the program proceeds or when the conditions arise. There are two tools in PL/SQL to design and execute dynamic queries.

1) Native Dynamic SQL (NDS)

2) DBMS_SQL package

NDS is relatively easy to use when compared to DBMS_SQL package. DBMS_SQL has a complex structure for making dynamic queries.

Using NDS, inputs can be passed to a query statement and outputs can be collected.

While writing dynamic queries, we can follow two approaches. Parts and inputs of a dynamic query can be attached to each other dynamically using string concatenation and then the dynamic query can be executed, or , placeholders can be used to pass parameters/inputs to a dynamic query. Using placeholder technique is a safer approach as it prevents SQL injection attacks.

Now let's see how to use NDS

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2)
 IS 
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; 
query_text := 'UPDATE employee_data SET salary=1.1*salary WHERE id = :1 RETURNING name INTO :2'; 
EXECUTE IMMEDIATE query_text USING user_id RETURNING INTO p_name;
COMMIT; 
END;

Create/replace the above procedure and call it. This dynamic query executes the update statement by taking id as input and returns the name of the employee who has got the hike.

Let's see one more example with select statement.

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2) 
IS
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; query_text := 'SELECT name FROM employee_data WHERE id = :1'; 
EXECUTE IMMEDIATE query_text INTO p_name USING user_id;
END;

This select statement saves the name of the employee in p_name variable for a given id.

For more info visit

How to emulate a remote Linux machine on Mac using X11 port forwarding in ssh ?

Remote Linux server/machine can be accessed from Mac using X11 port forwarding feature in ssh.

X11, also known as X window system, is a combination of server and client programs which can be used to emulate desktop environment of remote Unix like machine on a local machine. The server program of X window system runs on local machine and the client program runs on remote machine.

The best feature of X11 window system is that it is separated into client and server program, which makes it suitable to use both the programs either on same machine or on different machines. For ex, when you are using a Linux desktop with GUI, both client and server programs are running on the same machine and when you access your remote unix like machine from local machine then the server program of X window system runs on local machine and the client program runs on remote machine.

Separation of client and server programs makes X window system really fast over networked connections as most of the drawing work is handled on the local machine.

X window system can be really complex to use over networked connection as server is on the local machine, which makes it difficult for the client, running at remote machine, to see the server.

ssh provides a feature to handle this complexity, which is known as X11 port forwarding.

X11 port forwarding feature of ssh makes a secure tunnel between client and server programs of X window system, so that they can communicate .

Now let's see how to use this feature of ssh to connect to a remote Linux machine from a Mac.

You need to have an X11 server program on your mac to connect to the remote Linux machine.

X11 app comes default with the Operating system for Snow Leopard users, but it has to be downloaded and installed explicitly on Mountain Lion.

Mountain Lion users can download X11 app from here.

Once app is installed, you will have to perform some configuration changes in ssh server running on remote Linux machine.

Add the following lines to the file /etc/ssh/ssh_config

ForwardAgent yes

ForwardX11 yes

ForwardX11Trusted yes

After this uncomment/add the following line in /etc/ssh/sshd_config file.

X11Forwarding yes

Restart the ssh server on remote machine for changes to take effect.

Now on local machine

Open terminal and execute the following command

ssh -X user@host.com

This will log you in to the remote machine if the keys are set, else, it will ask for password.

After you are logged in

Assuming that the remote machine's desktop environment is GNOME, execute the following 

command.

gnome-session

If everything works perfectly, you will be able to see your remote Linux machine GUI on your Mac.

For more info visit.