TW Tech Glossary - Misplaced your bible? Well here it is! This truly took a while to complete and should be used by all from beginners to advance techies. Look into it, you won't be sorry. (Very Resourceful)

General Oracle Tutorial - This tutorial is a general Oracle Tutorial and should not be confused for Oracle Certification Tutorial. It will get any beginner up and running and will be a handy too for the advanced user as well.

What is Oracle?

Oracle is one of the most powerful database management system based on Relational Database Model, provided by Oracle Corporation along with fully integrated database application development and administration tools. It uses Structured Query Language (SQL) for database access and its own proprietary procedural language PL/SQL for application development along with Java programming support.

Tips

User Login Without Password

Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is, or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself. Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose. Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value. Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.

The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.

spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';'
from dba_users;
spool off

Getting Table Name and Probable Table Owner

Using the Defines Using Select tip, here is a snippet of SQL code that can be used to prompt for and get the table name from the user, as well as the probable default table owner (from the first dba_tables entry for that table name, or, if not found, using the current user's ID) which can be overridden by the user. The resulting entries are put into the tablename and tableowner variables as upper case values.

set showmode off echo off
set termout on
accept tablename char prompt 'Enter table name: '
set termout off
column tablename new_value tablename
select upper('&tablename') tablename from dual;
define tableowner = 'DUMMY'
column tableowner new_value tableowner
select owner tableowner from dba_tables
where table_name = upper('&tablename') and rownum = 1;
select username tableowner from user_users
where '&tableowner' = 'DUMMY' and rownum = 1;
set termout on
accept tableowner2 char prompt 'Enter table owner, if not &tableowner: '
set termout off
select upper(decode('&tableowner2','','&tableowner','&tableowner2'))
tableowner from dual;