INTRODUCTION TO ORACLE:
ORACLE IS A RELATIONAL DATABASE AN RDBMS PRODUCT
FROM ORACLE CORPORATION IN 1979.WHICH IS USED TO STORE
DATA (OR) INFORMATION PERMANANTLY i.e., IN HARD DISK ALONG
WITH SECURITY.
ORACLE IS A PLATFORM INDEPENDENT AN RDBMS
PRODUCT.IT MEANS THAT IT CAN DEPLOYEE (INSTALL) IN ANY OS
LIKE WINDOWS, LINUX, UNIX, SOLARIES, MAC.... etc.
PLATFORM:
- IT A COMBINATION OF OPERATING SYSTEM AND MICRO
PROCESSOR.THESE ARE AGAIN CLASSIFIED INTO TWO TYPES.
1) PLATFORM INDEPENDENT:
- IT SUPPORTS ANY OS WITH THE COMBINATION OF ANY
MICRO PROCESSOR.
EX: ORACLE, MYSQL, JAVA, .NET.... etc.
2) PLATFORM DEPENDENT:
- IT SUPPORTS ONLY ONE OS WITH COMBINATION OF ANY
MICRO PROCESSOR.
EX: C - LANGUAGE.
Versions of ORACLE:
Year Version Features
1979 Oracle 1.0 Not Public
released
1980 Oracle 2.0 First Public
released,
Basic SQL
functionalities.
1982 Oracle 3.0 First Portable DB.
1984 Oracle 4.0 Introduced read
consistency.
1986 Oracle 5.0 Introduced
client-server
architecture.
1988 Oracle 6.0 Introduced
PL/SQL
1992 Oracle 7.0 Integrity
Constraints
introduced,
Varchar data type
changed into
Varchar2,Stored
procedures,
functions and
triggers
1997 Oracle 8.0 Object Oriented
Features, Table
partitioning,
Instead Triggers
1998 Oracle Rollup, cube
8i(Internet) methods,
Columns
increased per a
table up to 1000
2001 Oracle 9i Renaming
Column,Ansi
Joins
2004 Oracle Introduced
10g(grid Admin side
technologies) operations,
flashback query,
Indicate of
clauses, regular
expressions
2007 Oracle 11g Read only tables,
virtual tables,
integer data type,
using sequence,
enables and
disables triggers.
2013 Oracle12c Truncate table
(cloud cascade, multiple
technology) indexes,invisiable
column, sequence
session, new auto
increment by
using
Identity.
2018 Oracle18c Polymorphic
Table Functions,
Active Directory
Integration
2019 Oracle19c Active Data
Guard DML
Redirection,
Automatic Index
Creation,SQL
Queries on Object
Stores.
WORKING WITH ORACLE:
WHEN WE INSTALL ORACLE SOFTWARE INTERNALLY TWO
COMPONENTS ARE INSTALLED.THOSE ARE,
1. ORACLE CLIENT
2. ORACLE SERVER
1. ORACLE CLIENT:
BY USING ORACLE CLIENT TOOL USER CAN PERFORM THE
FOLLOWING THREE OPERATIONS ARE
USER CAN CONNECT TO ORACLE SERVER
USER CAN SEND REQUEST TO ORACLE SERVER
USER CAN RECEIVE RESPONSE FROM ORACLE SERVER.
Ex: SQLPLUS, TOAD, SQL DEVELOPER, SQL NAVIGATOR………………….
etc.
2. ORACLE SERVER:
ORACLE SERVER MANAGE TWO MORE SUB COMPONENTS
INTERNALLY THOSE ARE,
INSTANCE
DATABASE
INSTANCE WILL ACT AS TEMPORARY MEMORY WHICH WILL
ALLOCATE FROM RAM AND STORED DATA / INFORMATION
TEMPORARY WHERE AS DATABASE IS A PERMANENT MEMORY
WHICH WILL ALLOCATE FROM HARDDISK AND STORED DATA
PERMANENTLY.
NOTE: WHEN WE WANT TO WORK ON ORACLE DATABASE THEN WE
FOLLOW THE FOLLOWING TWO STEPS PROCEDURE
1) CONNECT TO ORACLE:
IF USER WANTS TO CONNECT TO ORACLE THEN WE
REQUIRED A DATABASE TOOL IS CALLED AS “SQLPLUS”
WHICH WAS INBUILTED IN ORALCE SOFTWARE.
2) COMMUNICATE WITH DATABASE:
IF USER WANTS TO COMMUNICATE WITH DATABASE
THEN WE NEED A DATABASE COMMUNICATION LANGUAGE
IS CALLED AS “SQL”.
HOW TO CONNECT TO ORACLE:
BEFORE CONNECT TO ORACLE DATABASE WE NEED TO KNOW
THE TYPES OF EDITIONS IN ORACLE SOFTWARE.EVERY ORACLE
SOFTWARE IS HAVING TWO TYPES OF EDITIONS THOSE ARE
1) ORACLE EXPRESS EDITION (PARTIAL SUPPORTING FEATURES)
2) ORACLE ENTERPRISE EDITION (FULLY SUPPORTING
FEATURES)
THE ABOVE TWO EDITIONS ARE HAVING DEFAULT USERNAME IS
“SYSTEM” AND PASSWORD IS CREATED AT INSTALLATION OF
ORACLE SOFTWARE.
STEPS TO CONNECT TO ORACLE:
> GO TO ALL PROGRAMS
> GO TO ORACLE19c_HOME1 FOLDER
> CLICK ON SQL PLUSE ICON
> ENTER USERNAME: SYSTEM
> ENTER PASSWORD: MANAGER (AT INSTALLATION TIME
PASSWORD)
TO CREATE A NEW USERNAME & PASSWORD IN ORACLE DB:
SYNTAX: CREATE USER <USERNAME> IDENTIFIED BY
<PASSWORD>;
EX: CREATE USER SUDHAKAR IDENTIFIED BY SUDHAKAR;
NOTE: USER IS CREATED BUT THIS USER IS DUMMY USER BECAUSE
IS NOT HAVING PERMISSION TO CONNECT AND CREATE NEW TABLE
IN DB.SO PERMISSIONS MUST BE GIVEN TO USER(SUDHAKAR) BY
USING “GRANT” COMMAND BY DBA(SYSTEM).
NOTE: EVERY USER IN ORACLE SERVER IS CALLED AS “SCHEMA”.
GRANTING PERMISSIONS TO USER:
STEP1: USERNAME: SYSTEM
PASSWORD: MANAGER
CONNECTED.
STEP2:
GRANT CONNECT, CREATE TABLE TO SUDHAKAR;
Here,
CONNECT ------- TO CONNECT TO ORACLE DB
CREATE TABLE ------- TO CREATE NEW TABLES IN DB.
NOTE: WHEN WE CONNECT TO ORACLE DB SOME TIMES, WE WILL
FACE A PROBLEM IS,
ERROR: ORA-28000: THE ACCOUNT IS LOCKED.
TO OVERCOME THE ABOVE ERROR THEN WE FOLLOW THE
FOLLOWING STEPS ARE
SOLUTION:
STEP1: CONNECT TO ORACLE WITH SYSTEM DATABASE ADMIN:
SYNTAX:
ENTER USERNAME: SYSTEM
ENTER PASSWORD: MANAGER
CONNECTED.
STEP2: TO UNLOCK USER:
SYNTAX:
SQL> ALTER USER <USER NAME> ACCOUNT UNLOCK / LOCK;
EX:
SQL> ALTER USER SUDHAKAR ACCOUNT UNLOCK;
STEP3: NOW CONNECT TO ORACLE WITH EITHER SYSTEM (OR)
SUDHAKAR USER:
ENTER USERNAME: SUDHAKAR
ENTER PASSWORD: SUDHAKAR
CONNECTED.
HOW TO CHANGE A PASSWORD:
SQL> PASSWORD
Changing password for SUDHAKAR
Old password: SUDHAKAR
New password:123
Retype new password:123
Password changed
SQL> CONN
Enter user-name: SUDHAKAR / SUDHAKAR
ERROR: ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN
Enter user-name: SUDHAKAR / 123
Connected.
HOW TO CREATE A NEW PASSWORD IF WE FORGOT A
PASSWORD:
SYNTAX:
ALTER USER <USER NAME> IDENTIFIED BY
<NEW PASSWORD>;
EX:
Enter user-name: SYSTEM / MANAGER
Connected.
SQL> ALTER USER SUDHAKAR IDENTIFIED BY SUDHAKAR;
User altered.
SQL> CONN
Enter user-name: SUDHAKAR / 123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN
Enter user-name: SUDHAKAR / SUDHAKAR
CONNECTED.
NOTE: WHEN WE WANT TO CONNECT TO ORACLE DB SERVER
SOME TIMES, WE FACED ANOTHER PROBLEM IS CALLED AS
“TNS PROTOCAL ADAPTER ERROR”.
Enter user-name: SUDHAKAR / SUDHAKAR
ERROR:
ORA-12560: TNS: protocol adapter error
Enter user-name: SYSTEM / MANAGER
ERROR:
ORA-12560: TNS: protocol adapter error
NOTE: TO OVERCOME THE ABOVE PROBLEM THEN WE FOLLOW
THE FOLLOWING STEPS ARE,
STEP1: GO TO SERVICES
STEP2: GO TO ORACLESERVICEorcl AND CLICK ON IT
STEP3: SELECT STARTUP TYPE IS AUTOMATIC
STEP4: CLICK ON START BUTTON
STEP5: CLICK ON OK
Enter user-name: SYSTEM / MANAGER
Connected to: Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn
Enter user-name: SUDHAKAR / SUDHAKAR
Connected.
