Wonderful Oracle Faqs are in the following links

Link 1

1. Difference between Instance and Database?
The terms instance and database are closely related, but don't refer to the same thing. The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance; however, an instance can open at most one database.
2. How to connect to new database in oracle?
sqlplus username/password@connect_identifier
SQL> connect username/password@connect_identifier
To hide your password, enter the CONNECT command in the form:
SQL> connect username@connect_identifier
You will be prompted to enter your password.
In windows another ex usage :
SQL> connect sys@connect_identifier as sysdba
Enterpassword :
Connected.
3. How to create a new user in a particular database?
CREATE USER user_name IDENTIFIED BY password;
CREATE USER uwclass IDENTIFIED BY uwclass;
CREATE USER user IDENTIFIED {BY password |
EXTERNALLY}
4. How to alter a user?
ALTER USER sidney IDENTIFIED BY second_2nd_pwd DEFAULT TABLESPACE exmple;
ALTER USER sh PROFILE new_profile;
ALTER USER sh DEFAULT ROLE ALL EXCEPT dw_manager;
ALTER USER app_user1 IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';
ALTER USER sidney PASSWORD EXPIRE;
ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;
ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;
ALTER USER app_user1 REVOKE CONNECT THROUGH sh;
ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD;
5. How to see existing users in Oracle Database?
select name from sys.user$;
select username,password from dba_users;
6. How to change the existing user password in the present oracle database?
alter user myuser identified by my!supersecretpassword;
grant connect to myuser identified by my!supersecretpassword
update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
SQL*Plus command: password or password username
7. How to launch the database configuration assistant tool in Oracle?
Go to $ORACLEHOME/bin
And run the “dbca” binary.
/app/oracle/product/10.2.0/Db_1/bin/dbca
8.Oracle Versions
Oracle products have historically followed their own release-numbering and naming conventions. With the Oracle RDBMS 10g release, Oracle Corporation started standardizing all current versions of its major products using the "10g" label, although some sources continued to refer to Oracle Applications Release 11i as Oracle 11i. Major database-related products and some of their versions include:
• Oracle Application Server 10g (also known as "Oracle AS 10g"): a middleware product;
• Oracle Applications Release 11i (aka Oracle e-Business Suite, Oracle Financials or Oracle 11i): a suite of business applications;
• Oracle Developer Suite 10g (9.0.4);
• Oracle JDeveloper 10g: a Java integrated development environment;
Since version 7, Oracle's RDBMS release numbering has used the following codes:
• Oracle7: 7.0.16 — 7.3.4
• Oracle8 Database: 8.0.3 — 8.0.6
• Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
• Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
• Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
• Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5 (Latest current patchset as of December 2003)
• Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8 (Latest current patchset as of April 2007)
• Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5 (Latest current patchset as of February 2006)
• Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.3 (Latest current patchset as of November 2006)
• Oracle Database 11g Release 1: 11.1.0.6 — no patchset available as of October 2007
The version numbering syntax within each release follows the pattern: major.maintenance.application-server.component-specific.platform-specific.
For example, "10.2.0.1 for 64-bit Solaris" means: 10th major version of Oracle, maintenance level 2, Oracle Application Server (OracleAS) 0, level 1 for Solaris 64-bit.

9. How to see exixsting Oracle Version on the system ?
1)select * from v$version;
10.How do we know which version of oracle we are using ?
I need to know whether it is 32 bit Or 64 bit.

From the unix prompt enter , then enter
bash-2.05$ file oracle
a. A 32 bit oracle server will return:
oracle: ELF 32-bit MSB executable SPARC Version 1,
dynamically linked, not stripped.

b. A 64 bit oracle server will return:
oracle: ELF 64-bit MSB executable SPARCV9 Version 1,
dynamically linked, not stripped.

11.How to see the Patches applied on existing Oracle

$ORACLE_HOME/OPatch/opatch lsinventory

opatch does not list the patches applied on DB. it lists the interim patches applied on oracle binaries.

the patched applied on DB are listed with
SQL> select * from registry$history;



How to create a password policy to not to use the used password for any users?

CREATE PROFILE krish LIMIT
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX 10;COMMIT;

 /* when a user is assigned with above policy he cant reuse the password again */

-- Add user CDSSCHEMA. This MUST exist for Oracle schema creation.
-- CDS explicitly addresses the schema, and they way Oracle
-- names a schema is by the user name that creates it.
-- The password should be changed from the default value 'tivoli'.

CREATE USER CDSSCHEMA
  IDENTIFIED BY oracle
  DEFAULT TABLESPACE cds_ts123
  TEMPORARY TABLESPACE cds_temp_ts123
  QUOTA UNLIMITED ON cds_ts123
  PROFILE krish;
COMMIT;

The above will create a user called CDSSCHEMA and the he will be under profile krish and hence he cant re-use the same password again.

GRANT CONNECT, RESOURCE, ALTER SESSION, CREATE SEQUENCE, CREATE SESSION,
      CREATE SYNONYM, CREATE TABLE, CREATE VIEW, UNLIMITED TABLESPACE
  TO CDSSCHEMA IDENTIFIED BY oracle ;
COMMIT;

the above will through error because of the not to use used passwords policy.
/* GRANT will reset the passowrd to new one , it will change the existing password if we specify identified by is given */
 Error sample

GRANT CONNECT, RESOURCE, ALTER SESSION, CREATE SEQUENCE, CREATE SESSION,
*
ERROR at line 1:
ORA-28007: the password cannot be reused

How to avoid overlapping of  columns when working on SQL prompts (DOS/UNIX )?
SQL> set wrap off


How to upgrade Oracle 9i(or lower) version to 10g ?

Oracle 9i to 10g

ORcle 9i to 10g upgrade.pdf