How to access table in different schema in Store Procedure

On one Oracle database, there are several users or schemas. Each schema contains tables. These table belong to each user. It is possible for a user to access table in other schema. For instant, if there are three users in Oracle database, such as USER1, USER2, and USER3.

In event that the USER3 needs to access the table which belongs to the USER2. SQL select statement is written as

Select Col1, Col2, Col3 from USER2.TableName

This statement works fine when the SQL statement is run in Toad or SQL-Plus. However, if the SQL statement above is put into the store procedure, it will not work. The store procedure will not compiled correctly.

To correct this problem, the USER2 has to grant select on table privilege the USER3. Follow the step below:

1. Login to the Oracle database as USER2
2. Run this statement Grant Select on USER2.TableName to USER3;

Now the USER3 can put the select statement of granted table in the store procedure.

Rename DATA FILE for Oracle

The data file is created when the Oracle DBA created the table space. If the Oracle DBA wants to rename the data file after the it has been created, he or she can do so by following these steps

1. The table space has to be taken offline; use the following command ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
2. Then data file is rename using the operating system.
3. After that the Oracle DBA renames the data file in SQL Plus enviroment. Supposed that the old data file name is 'USER1.DBF'. The new data file name is 'NEW_USER.DBF'. Following the step 2, issue this SQL comment ALTER TABLESPACE 'tablespace name' RENAME DATAFILE 'C:\USER1.DBF' to 'C:\NEW_USER.DBF'; This assumes that the data file is located in drive C
4. After rename the data file is performed in the Oracle environment, mount the tablespace online again, using this command: ALTER TABLESPACE tablespace_name ONLINE;

Now, the table space contain the data file with the new name.

How to undrop a table in Oracle

When a table in the Oracle databaseis dropped, table is not actually dropped. The dropped table is renamed to other name which starts with BIN$.... The table is stored in recycle area, so the table space is still occupied by the dropped table. This is the new feature in Oracle version 10G. We will try to create the new table, insert data, drop and undrop the table.

SQL> create table testing (col varchar2(10), row_chng_dt date);

Table created...

SQL> insert into testing values ('Version1', sysdate);

1 row created..

SQL> drop table testing;

Table dropped.

Now the deleted table is stored in the recycle area. To see the dropped table in the recycle area, issue this SQL commend.

SQL> select object_name, original_name, type, droptime

from recyclebin Where original_name = 'TESTING';

the result is shown below

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

------------------------------ ------------- ----- ---------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12

If the user makes the select statement again, the user shall use BIN$HGnc55/7rRPgQPeM/qQoRw==$0 for table name

Select * from BIN$HGnc55/7rRPgQPeM/qQoRw==$0;

Now the user can restore the table by using this SQL statement

SQL> flashback table testing to before drop;

Flashback complete.

In case that we want to completely delete table Testing from database. We have to purge table from the recycle bin area.

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

Table purged.

After purging operation is complete, the table space in the Oracle is released.
Hone your skill on the Oracle database with this book

Creating DB User and Grant Option - Oracle

When the new user has been created in Oracle database, the new user cannot do anything with the database. The database admin has to grant privilege to that user. First privilege is

SQL> grant connect to Tommy;

Grant succeed.

The privilege allow the user Tommy to be able to connect to the database. In order for user Tommy to use the resource, granting resource must be follow:

SQL> grant resource to TOMMY;

Grant succeeded.

Now, the user Tommy can use resource like table space. There are other options in privilege to be granted. From my practice, after I create the new user, I also grant EXP_FULL_DATABASE to the user.

SQL> grant EXP_FULL_DATABASE to TOMMY;

This option allows the user to create and drop tables and other objects.

Oracle Password Management

Each user account is protected by the their password. When the user is created, the password is assigned to the user. By default, user password is set to expired by 180 days. After the password expires, the user can no longer access his or her account. To see the expiry date for each open user, user following commands.

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

The result shall display after query statement execution. The database administrator can switch off password expiry option by issuing the following command

SQL> Alter Profile Default limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

Now running the same query below, the expiry date value shall be null

SQL> Select username, Account_Status, Expiry_Date from DBA_USERS;

However, this is dangerous for security reason because all users under PROFILE default have non-expiry date for password. To limit the password expiry again,

SQL> alter profile default limit PASSWORD_LIFE_TIME 180;

Profile altered.

180 means password expire after 180 days

Oracle Password Restriction & Solution

What are valid characters in password for Oracle? The Oracle recommends using the combination of letters and numbers. Symbols of "#", "_", and "$" sign are also valid. There are rules that you should follow when creating the password.

First Rule: The first letter of the password must start with the letter, Example

SQL> alter user tommy identified by tommy1234;

User altered.

SQL> alter user tommy identified by 1234tommy;

ERROR at line 1:

ORA-00988 missing or invalid password(s)

As you can see, the password cannot start with the number. However if you want to start password with the number, you can use the double quotation mark on the password;

SQL> alter user tommy identified by "1234tommy";

User altered.

My note: some blogs say password is not case sensitive. I do some testing on the testing database. The result is password is case sensitive. If your user password is Tommy1234, typing password as tommy1234 gives the error message while logging in.

Good luck........

Alter User Password - Reset User Password in Oracle

When the database administrator creates a new user in the Oracle database, password for the new user is set to expire. Password expiry occurs, for it is the default in setting inside Oracle. You can query the Oracle database to see the detail of the database profile as:

Select Profile, Resource_Name, Resource_Type, Limit

from dba_Profiles

where Resource_Name like 'PASS%';

Then, look for highlight line. The password expire after 180 days.

Then, you can query the DBA_USERS table to see the expiry date for the user. Using the follow SQL statement:

Select UserName, Account_Status, Expiry_Date

from dba_Users

Where UserName = 'TEST';

The above query will show the account status and password expiry date for user TEST.

The password for the user TEST shall expire on Sep 03, 2011. What happen when your user password expires. The user cannot log in. Let's try by altering the user TEST account status to expire. Log in the Oracle database as SYSDBA in SQL Plus, and issue alter statement as follow:

Alter User TEST Password Expire;

Now the user TEST shall not be able to connect to the database. Here is the result after altering password to expired

You can unlock the account for user TEST by resetting the new password. At the SQL Plus command prompt, issue the following command to reset password

Alter User TEST identified by NewPassword;

The user TEST now can access the database again.....