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.....

Oracle PFILE vs. SPFILE Parameter File to Start up Oracle Instance

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

SPFILEs provide the following advantages over PFILEs:

• An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
• Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
• Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
• Easy to find - stored in a central location

What is the difference between a PFILE and SPFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

How will I know if my database is using a PFILE or SPFILE:

Execute the following query to see if your database was started with a PFILE or SPFILE:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"

FROM sys.v_$parameter WHERE name = 'spfile';

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.

Starting a database with a PFILE or SPFILE:

continue with this URL http://www.orafaq.com/node/5