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