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.