Easily change your Oracle password

From Oracle 8 on, you can use the command:

SQL> password

in SQL*Plus to easily change your password. By using

SQL> password <username>

you can change the password of another user.
This method has the advantage that the new password is not echoed on the screen.

The VSIZE function

 

To determine the exact number of bytes occupied of a table. The function 'VSIZE' reports the exact number of bytes allocated by Oracle for data.

For example, to determine the size of a table called 'fuzzy_pink_slippers' with columns 'col_a', 'col_b', ...'col_n', execute the following SQL statement to find the exact size of the table:

SELECT SUM (VSIZE (col_a)) + SUM (VSIZE (col_b)) + ... +
SUM (VSIZE (col_n))
FROM fuzzy_pink_slippers;

Performace / Set Timing On

We  can measure the performance of different queries by setting TIMING:

 

Set Timing on

 

Now, each time we run a query, we will see the time it took Oracle to solve it at the end. This is especially good when we are debating on which way is faster (e.g., a join or a sub query).

Commit / No Commit

Commit will always tell you commit complete But that does not necessarily mean you got what you wanted. Try typing commit; without doing anything, and you will see that you will get the same message. You need to know when oracle does a commit without your asking. For example, creating a table commits the previous information.

 

The Following actions generate an implicit commit:

 

1. Quit or exit

2. Create a Table or View (DDL)

3. Drop table, view, and grant

4. Revoke

5. Connect, Disconnect

6. Alter, Audit, noAudit.

We can conclude that the commands like update which just changes the contents are not commited and commands like alter, drop which manipulates the table structure are autocommited. It is from the perspective of the table manipulations.

Creating a Spool File

Male sure you save each session. This is especially useful at the beginning of your learning experience. Spool files help you analyze the mistakes you make and allow you to continue at a later time as opposed to start all over again. To Create a spool file, you can type:

 

SQL> spool c:\My Documents\sessionName.lst

 

The .lst(list) qualifier is nto required, but is highly recommended.

When you complete your session, it is a good practice to close your spool file :

 

SQL> Spool off;

 

SQL Scripts

You can type each SQL Command at the SQL> prompt and end it with a semiconlon - ; This Works fine. You might however, choose to write your commands and execute them altogether, by running a script.

 

To run a script, you should follow these steps:

 

1. Using the tect editor of your choice(e.g., Notepad, TexPad, WordPad), create a file called myCommands.sql. The .sql qualifier is not required, but is highly recomended.

 

2. Scripts can contain commnets. the convention for writing comments is: /* Your comments here, not sql Commands */. You should comment all you scripts. Imagine you want to figure out what you did 6 months down the road!!! you will be thankful you took the time to write comments.

 

3. Run the script. For example: @C:\My Documents\scriptname.sql will exdcute the script called scriptname.sql located in the My Documents Directory on your C drive.

 

Uid function

In Oracle/PLSQL, the uid function returns the id number for a user's session (the user who is currently logged in).

The syntax for the uid function is:

select uid 
from dual;

For example:

select uid 
into parm_uid
from dual;

The variable called parm_uid will now contain the id number for the user's session.  The value could be 25, for example.

 

SQLERRM Function

The SQLERRM function returns the error message associated with the most recently raised error exception.  This function should only be used within the Exception Handling section of your code:

EXCEPTION
    WHEN exception_name1 THEN
        [statements]

    WHEN exception_name2 THEN
        [statements]

    WHEN exception_name_n THEN
        [statements]

    WHEN OTHERS THEN
        [statements]  

END [procedure_name];

   
You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-2 0001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;
  
 

ETL- Extract, Transform, and Load

DEFINITION:

ETL stands for extract, transform and load, the processes that enable companies to move data from multiple sources, reformat and cleanse it, and load it into another database, a data mart or a data warehouse for analysis, or on another operational system to support a business process.

chk out this link to know more...