ORACLE DATAPUMP

ORACLE DATAPUMP
-----------------------------

DataPump Introduction
----------------------
 DataPump: DataPump is built in utility in oracle  to take logical backups its a technology high speed of data movement data and metadata from one database to another database 
 Datapump is available on oracle 10 g release 1 and later it is faster and more flexible alternatives to the traditional exp and imp. datapump runs with in the 
 oracle server processes and can read directly from database files and writes directly to file on the server, Data Pump is an extension to traditional EXP/IMP which
 provides more advantages like security ,speed etc,traditional export and import was mainly security problem for eg : we take take export scott schema .dmp file(dump file)
 we can easily take the scott.schema .dmp file import to any of the database machine traditional export and import does not provide any security to the .dmp file  

How Datapump Works:
-------------------
DataPump Export: oracle Datapump will create master table in the corresponding schema and data will be transferred parellely to dump file(.dmp)

DataPump Import: Oracle Datapump will happen reverse order that is dump file to master table will be created and from that original table

after finishing either export or import in datapump oracle will automatically drops the master table

Note: Whenever datapump export is done using parallel option,import also should be done with the same option,otherwise it will effect the time taking for import

sqlplus directory should be created both the exportdp and importdp

os level directory should be created both the exportdp and importdp

Datapump directory is a secure feature

You must create a directory in os level and DB level

On Target Database,you need to again create directory to use datapump

The Directory name can be different on target server

This adds one more layer of security for export/import

The Following Levels of Datapump export/import are possible
-------------------------------------------------------------

1)Database Level
2)Schema Level
3)Table Level
4)Row Level
5)Tablespace Level

   
in this  i am showing testing server (source) and production server (target)
----------------------------------------------------------------------------
first create directory at OS level

# mkdir -p /u02/dp_exp_dir

second create a directory at sql level

sql> create directory datapump as '/u02/dp_exp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

To view directory information

sql> select * from dba_directories

production server
----------------- 


# mkdir -p /u02/dp_exp_dir

second create a directory at sql level

sql> create directory datapump as '/u02/dp_exp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

To view directory information

sql>select * from dba_directories;

Test server
-----------

# mkdir -p /u02/dp_imp_dir

sqlplus '/as sysdba'

second create a directory at sql level

sql> create directory datapump as '/u02/dp_imp_dir';

craete or repalce directory datapump as '/u01/imp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

to know options of datapump export

$expdp help =y

To take the database level export
-----------------------------------

$expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

To take the schema level export
-----------------------------------

$expdp directory=datapump dumpfile=scott_bkp.dmp logfile= scott_bkp.log schemas='scott'


To take the table  level export
-----------------------------------

$expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log table='SCOTT.EMP';( here scott is the owner and emp is the table)


To take the row level export
-----------------------------------

$expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables= 'SCOTT.EMP' query=\"where deptno=10"\


production server
-----------------

$expdp directory =datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

sqlplus '/as sysdba'

$cd /u02/dp_exp_dir/

db_exp_dir] $ ls -lrt

scott_bkp.log
scott_bkp.dmp

$expdp directory= dataump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

To Import a full database
----------------------

sqlplus '/as sysdba'

$impdp directory= datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

To know options of datapump import

$ impdp help =y

To Import a schema
----------------

$impdp directory =datapump dumpfile=scott_bkp.dmp logfile= imp_schema.log remap_schema='SCOTT:SCOTT'

$impdp directory= datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema ='SCOTT:ABC'


To Import a table
--------------
$impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP" remap_schema='SCOTT:SCOTT'

To Import a table to another user
-------------------------------

$impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables= 'EMP" remap_schema= 'SCOTT:system'


To Import a table to another tablespace(only in datapump)
------------------------------------------------------
$imp directory= datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='emp remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'

Production server
-----------------
$db_exp_dir]$ ls -lrth

scott_bkp.dmp

$scp scott_bkp.dmp chaitanya@192.168.1.100: /u02/dp_imp_dir (production to testing server copy scott_bkp.dmp to /u02/dp_imp_dir location in testing server)  
password

Target server
-------------
$cd /uo2/dp_imp_dir
dp_imp_dir]ls -lrth
scott_bkp.dmp

$impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_scott.log remap_schema='SCOTT:IMP_TEST  (here user is imp_test scott is owner)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle - datapumps - Logical Backup
------------------------------------

Note : For Logical backup, the DB must be up and running.

using logical backup, will move the data from one server to another server.

with utilities , expdp and impdp will export and import in to target database.

prod - expdp - .dmp - > move to target server using scp
---> .dmp - impdp ->import - target 

Prior to 10g version , we have exp and imp. From 10g oracle introduced Oracle datapumps with expdp and impdp which is 15-45 faster than normal imports.

We also use logical backup for refresh activities.

If developers want to build a module , for that they need production data to development server.
So DBA will refresh data from prod to develop server as per critirea.

There are three levels of Logical backup.

1. Full Database
2. Schema Level
3. Table Level

Export Parameters - job
------------------------
Will export in to binary dump file .dmp with expdp utility.

To get help on export parameters.
--------------------------------
$expdp help=y

Use physical location to hold dump files.
$mkdir -p /orabackup/prod/dp


Create logical alias for physical directory.
----------------------------------------
sql>create or replace directory dp_dir as '/orabackup/prod/dp';
sql>grant read,write on directory dp_dir to public;


Verify - the path /name - dba_directories
------------------------------------------
SQL> select directory_name,directory_path from dba_directories where directory_name like 'DP_DIR';

Two grants needed for a user to export and import.

datapump_exp_full_database
datapump_imp_full_database

SQL> select role from dba_roles where role like '%DATAPUMP%';

eg: If scott user to have a backup
sql>grant datapump_exp_full_database to scott;
sql>grant datapump_imp_full_database to scott;

Default system user has these roles. so can use system user to
have logical backup.
---------------------------------------------------------------
Full Database backup
--------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720.dmp logfile=expdpfull220720.log full=y compression=ALL job_name=j1

Interactive Method -
-------------------- 
can start/continue/stop/status/parallel - jobs

To stop the dp job.
--------------------

use ctrl+c
export>stop
export>....yes

Verify the job - status
-------------------------

sql>select job_name,state,operation from dba_datapump_jobs;

To continue - the dp job

1. attach
2. continue.

$ expdp system/sys123 attach=P1
export>continue


To terminate the job - kill.

use ctrl+c
export>kill
export>yes.

Schema Level Backup
--------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpscott.dmp logfile=expdpscott.log schemas=scott,hr,sh job_name=t1 compression=ALL content=metadata_only

Table Level backup
-------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpscotttab.dmp logfile=expdpscotttab.log tables=scott.emp,hr.departments job_name=r1 compression=ALL


How to diagnose the errors while logical backup? either expdp/impdp.
---------------------------------------------------------------------

$more expdpscotttab.log 
will have messages 
successfully completed
abnormally terminated
No space left on device (clean up backup space)
object doesnot exists
user doesnot exists
user already exists
objects skipped


Parameters
----------
Full
Schemas
Directory
tables
dumpfile
logfile
content
compression
job_name
parfile

Using parfile 
-------------
is a parameter file. will enclose the parameters in parfile.

Create parfile using vi

$vi expdpfull.par
directory=dp_dir
dumpfile=expdpfull.dmp
logfile=expdpfull.log
full=y
job_name=p1
estimate=blocks

Provide permissions - execution
$chmod +x expdpfull.par


$expdp system/sys123 parfile=expdpfull.par

using parameter estimate/estimate_only

  with estimate=blocks|statistics

will estimate the size of the dump file while export.
in terms of blocks level or using statistics

With estimate_only
will show the dumpfile size , but not export.


$expdp system/sys123 directory=dp_dir full=y estimate_only=yes

Finding usage of each folder in current location
------------------------------------------------
#du -sh *

Error: Severity 1
/u01 threshold limit reached 90% 
Action:

inventory
binaries
network files
alert log file / trace files --- will cause full - house keep

Error : Sev2
/oradata - 90% threshold reached?
action:
find the maxbytes with autoextend on 
limit with autoextend on
find space to clean up if any other files
Find system engineer to add more space.
find other fs if so, to add more datafiles.


Number of databases on server?
/etc/oratab

Number of instances currently running on server?
ps -ef | grep pmon
or
ps -ef| grep smon

Number of instance can start on boot?
/etc/oratab
Y/N


CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

To export only table structure and with out data
can use content parameter with metadata_only
default is all(includes data)

eg:
$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log schemas=scott,hr  content=metadata_only


Note: 
To estimate_only , finding the size of the dumpfile.
        Doesnt need to specify the dumpfile and log file.
Will not export. Just estimates.
------------------------------------------------------------
Parallel
--------

Can write/load in to multiple dumpfiles for large database.
For faster performance.
using parallel parameter , depends on number of CPU's
eg: Number of cores : 2 
parallel=2

eg:
$expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720_%U.dmp logfile=expdpfull220720.log schemas=scott,hr,sh parallel=2

%U - unique number


COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.


EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

QUERY : Specific rows/records can unload.
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log QUERY='scott.emp:"WHERE deptno > 10"' 

VERSION
--------
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott037.dmp logfile=expdpscott0307.log QUERY='scott.emp:"WHERE deptno > 10"' version=12.2

eg: version=12.2  or version=LATEST
can use for migrating data from source to target of different version.
from 12.1.0  - ? 12.2.0

-------------------------------------------------------
Imports
----------
till now,...using expdp utility dumped in to .dmp file.

Now from source DB will dump into .dmp and scp to remote server
using source .dmp file will import into target DB

Note: scp - server copy
eg:
$scp  filename  root@servername:/path
eg: $scp expdpscott.dmp oracle@chaitanyahost:/orabackup/dev/dp
Paswd: oracle123
using impdp will import in to target db.

prod -> expdp -> .dmp - scp
->>>> .dmp -> impdp - > target db

$impdp help=y

The rqmnt
full
table
schema
with/without
compresed
query
version

having dump file from exports
will use to import in target db.

full ---> full/schema/table/query
Schema -> schema/table/query
table -> table/query

Finding Count of objects in schema before export to validate in source and target.
schemas exists in both source and target.
Tablespace associated with schema in target db as same as source.

eg:
source target
scott scott
  users users
Note: In target db, if schema user doesnt exist, will create automatially while import.
But make sure tablespace exists in target same in source before import.


sql>select object_type,count(*) from all_objects where owner like 'CHAITANYA' group by object_type;

select object_type,count(*) from all_objects where owner like 'ABHIRAM' group by object_type;

Count of Invalids - plsql objects will become invalid
-----------------
sql>select owner,object_type,count(*) from all_objects where status like 'INVALID' group by object_type,owner;

PLSQL objects - procedures/packages/functions/triggers will go off invalid.


Execute following script - to validate and compile.
--------------------------------------------------
sql>@?/rdbms/admin/utlrp.sql

sql>select username,default_tablespace from dba_users where username like 'SCOTT';

sql>select tablespace_name from dba_tablespaces where tablespace_name like 'USERS';

----------------------------------------------------------
Scheme Level
-------------
$ expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720scott.dmp logfile=expdpfull220720scott.log schemas=scott

$impdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=impdpscott220720.log schemas=scott

Remap Schema
------------
unload/load in to different schema.
using
remap_schema=scott:manasa

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log schemas=scott

$impdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=impdpscott220720.log schemas=scott remap_schema=scott:manasa

sql>select username from dba_users where username like 'DEV';

sql>select object_type,count(*) from all_objects where owner like 'DEV' group by object_type;


Again reimport , what will happen?

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at

ORA-39151: Table "DEV"."SALGRADE" exists.a will be skipped due to table_exists_action of skip

ORA-31684: Object type USER:"DEV" already exists

--------Still , want to continue reimport - how?

Using table_exists_action parameter in impdp
--------------------------------------------
$impdp system/sys123 directory=dp_dir dumpfile=expdpfull220720scott.dmp logfile=impdpscott220720.log schemas=scott remap_schema=scott:dev table_exists_action=truncate

Note: truncate , will delete the table records and can reuse the space.


TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version


TABLESPACES
Identifies a list of tablespaces to import.


TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_2020.

SCHEMAS
List of schemas to import.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.


QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".


NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

FULL
Import everything from source [Y].

HELP
Display help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

LOGFILE
Log file name [import.log].


EXCLUDE
Exclude specific object types.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.


CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.



Export Import
full full/query/table/content/schema
schema schema/table/query/content
table table/query/content




Note: Here i am giving some info on datapump which may differ from your environment like production, testing and ipaddress  etc.




THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG REGULARLY  https://chaitanyaoracledba.blogspot.com/

USER MANAGEMENT IN ORACLE

USER  MANAGEMENT IN ORACLE

Introduction:

In this blog i am going to expalin USER MANAGEMENT IN ORACLE,User is basically used to connect a database  it may be production, pre-production,development or testing server (UAT) but users  depends on  what the role and designation it should be manager, admin or programmer,test user 

all database objects like Table,index,view,etc can be created under the user,in oracle users,and schemas are important you can consider that user is the account connect to database ,and schema is the set of objects (tables,vies,indexes).

USER MANAGEMENT IN ORACLE that belong to the account any user minimum privelige is necessary to connect a database it should be like admin priviles   

User Management  - SQL

Create user ( creating the user in database)

alter user  (alter the user in database)

drop user   (drop the user in database)

sql>create user chaitanya identified by chaitu123;

Already exists/list of user / got created.

Using dictionary view/table owned sys user.

Information from dba_users

sql>desc dba_users;

sql>select username,password,account_status from dba_users where username like 'CHAITANYA';

sql>select username,password,account_status from dba_users where username in ('CHAITANYA','SYS','SCOTT');

sql>select distinct account_status from dba_users;

Locked

Expired & Locked

Open

Account Lock/Unlock/reset

sql>alter user Chaitanya account lock;

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account unlock;

sql>alter user chaitanya identified by chaitu123;

Dropping user

sql>drop user chaitanya;

Creating user :

sql>create user abhiram identified by abhi123;

sys owned dictionary tables, can find the information of user details.

Note : dba_users

sql>select username,account_status from dba_users where username in ('CHAITANYA','MANASA','ABHIRAM','SAIBABU','PAVAN','VASANTH','SYS','SYSTEM');

sql>select username,password from dba_users;

Finding number of users in DB

sql>select count(*) from dba_users;

Altering user 

Account lock/unlock

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account lock;

sql>alter user chaitanya account unlock;

Password reset / Changing

sql>alter user Manasa identified by manu123;

Drop user - removing user from db

sql>drop user abhiram;

   

    note: including dependencies , his objects to remove.

 

      If its a schemas, then use cascade.

sql>drop user abhiram cascade;

    In case, scott user

 

    sql>drop user scott cascade;

Roles :

Roles are groupings or collection of privileges that you can use to create different levels of database access.Defining subset of privileges through a role to a user.it allows easier management of privileges in USER MANAGEMENT IN ORACLE

We can also grant role to a role and to a user.

• manager
• dba
• developer
• tester
• privilege - > user
• privilege -> role - > user
• privileges - > role - > role - > user
• 
  
• 
  
•         dba_roles
•         dba_role_privs
•         role_role_privs
•         dba_sys_privs

sql>select role from dba_roles;

ROLE

------------------------------

CONNECT

RESOURCE

DBA

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

Creating role:

sql>create role dev_role;

sql>create role test_role;

sql>select role from dba_roles where role like 'DEV_ROLE';

How to grant role to a user?

sql>grant dev_role to scott;

Connect role :

Connect : is a system defined role with create session privilege.

    Every new user needs create session privilege.That granted with role connect.

Privilege(create session) ---> user (abhiram) 

SQL>grant create session to dev_role;

Privilege(create session) -> connect(role) -> user (abhiram)

privileges - > role(connect) - > role(dev) -> user

dev(role) -> user will get including connect role 

sql>grant connect to chaitanya;

sql>connect chaitanya/chaitu123;

Resource role : built in system defined role

    resource role has create objects privileges.

    create table,create index,create trigger

SQL> select privilege from dba_sys_privs where grantee like 'RESOURCE';

SQL> select privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select privilege from dba_sys_privs where grantee like 'DBA';

SQL> select privilege from dba_sys_privs where grantee like 'DEV_ROLE';

sql> grant dba to scott;

Now scott has DBA role, have all privileges.

sql>grant resource to abhiram;

  role - role ----> user

  privilege ---> role ----> user

  privilege ---> user

  privilege ---> role ---> role ---> user

  create session ---> connect,resource ---> dev_role---> abhiram

  create user,drop user---> dev_role ---> abhiram

DBA Role :

        If user does need all sys privileges to manage database. 

provide DBA role which has all system privileges.

sql>grant dba to chaitanya;

sql>revoke dba from chaitanya;

Instead of giving connect,resource or other roles.we can create and grant with one single role.So we can grant role to role.

sql>grant connect,resource to  dev_role;

sql>grant dev_role to manasa;

So now manasa has create session,create table,trigger,index privileges via dev_role.So for next new user, directly we can grant with one single role.

sql>grant dev_role to vasanth;

What roles granted for a role?

sql>select granted_role from role_role_privs where role like 'DEV';

What roles granted for a user?

SQL> select grantee,granted_role from dba_role_privs where grantee like 'PAVAN';

Dropping a role:

sql>drop role dev_role cascade;

 Note : use cascade, to remove for dependencies

user-----> create/alter/drop

Role ----> create/drop/assign/revoke

Privileges/Permissions

Two Types of Privileges

1. System Level Privileges
2. Object Level Privileges

dba_tab_privs - Object Level Privileges---->(select,insert,update,delete)

sql>desc dba_tab_privs

dba_sys_privs - System Level Privileges---->(shutdown,backups,create user,alter user,create role,drop role,create table,alter table)

sql>desc dba_sys_privs

System Privileges -

SQL> select grantee,privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select grantee,privilege from dba_sys_privs where grantee like 'RESOURCE';

List of system Privileges

sql>select distinct privilege from dba_sys_privs; 

CREATE USER

SELECT ANY TABLE

CREATE SESSION

CREATE TABLESPACE

CREATE PROFILE

ALTER USER

DROP USER

How to grant /provide system privilege to scott ?

sql>grant create user,drop user,alter user to scott;

SQL> select privilege from dba_sys_privs where grantee like 'SCOTT';

To remove/revoke privileges

sql>revoke drop user from scott;

Privileges--->(Create session,create table..)--->Role(Connect,resource)---> role(devop)---> User(ravi)

sql>create user chaitanya identified by chaitu123;

sql>create role devop;

sql>grant connect,resource,create user,drop user to devop;

sql>grant devop to chaitanya;

sql>create user manasa identified by manu123;

Now grant role devop to manasa(developer)

sql>grant devop to manasa;

sql>revoke drop user from devop;

    but this applicable for all users under devop role.

Object / Table Privileges

sql>desc dba_tab_privs

SQL> select owner,grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>grant select,insert,update on scott.emp to hr;

SQL> select grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>revoke update  on scott.emp from hr;

sql>connect hr/hr123

sql>select * from scott.emp;

Profiles:

       Profile enforces set of password security rules and resources usage limit while creating a user if no profile is mentioned, then DEFAULT profile will be assigned to the user ,limiting resources for a user defining a profile.USER MANAGEMENT IN ORACLE

sql>desc dba_profiles

       

    system defined---> two profiles

        default

        monitoring_profile

SQL> select resource_name,limit from dba_profiles where profile like 'DEFAULT';

Altering profile:

SQL> alter profile devop limit idle_time 380;

create profile:

sql>create profile devop limit idle_time 180 failed_login_attempts 3;

Assign a profile to a user:

SQL> alter user scott profile devop;

Verify:

SQL> select username,profile from dba_users where username like 'SCOTT';

Drop profile :

sql>drop profile devop;

If user dependency , then use cascade to drop

sql>drop profile devop cascade;

• dba_users - username,paswd,profile,account_status
• dba_roles - roles
• dba_role_privs
• role_role_privs - roles
• dba_sys_privs - system level privs
• dba_tab_privs - object level privs
• dba_profiles - profiles details - default

Here i am giving some tables create it in your database and assign to user and roles in  

USER MANAGEMENT IN ORACLE

Tables

• agents
• customers
• orders

sql > CREATE TABLE  AGENTS("AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,AGENT_NAME CHAR(40),WORKING_AREA CHAR(35),COMMISSION NUMBER(10,2),PHONE_NO CHAR(15),COUNTRY VARCHAR2(25));

sql>CREATE TABLE CUSTOMER(CUST_CODE VARCHAR2(6) NOT NULL PRIMARY KEY,CUST_NAME VARCHAR2(40) NOT NULL,CUST_CITY CHAR(35),WORKING_AREA VARCHAR2(35) NOT NULL,CUST_COUNTRY" VARCHAR2(20) NOT NULL, GRADE NUMBER,OPENING_AMT NUMBER(12,2) NOT NULL,RECEIVE_AMT NUMBER(12,2) NOT NULL,PAYMENT_AMT NUMBER(12,2) NOT NULL, OUTSTANDING_AMT NUMBER(12,2) NOT NULL,PHONE_NO" VARCHAR2(17) NOT NULL,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS);

sql>CREATE TABLE ORDERS(ORD_NUM NUMBER(6,0) NOT NULL PRIMARY KEY,ORD_AMOUNT NUMBER(12,2) NOT NULL,ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL,ORD_DATE DATE NOT NULL,CUST_CODE VARCHAR2(6) NOT NULL REFERENCES CUSTOMER,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS,ORD_DESCRIPTION VARCHAR2(60) NOT NULL);

 Inserting values into agent table values

sql>INSERT INTO AGENTS VALUES ('A003', 'vasanth', 'Bangalore', '0.15', '077-25814763', 'india');

sql>INSERT INTO AGENTS VALUES ('A006', 'saibabu ', 'hyderabad', '0.13', '075-12458969', 'india');

sql>INSERT INTO AGENTS VALUES ('A009', 'pavan', 'rajahmundry', '0.12', '044-25874365', 'india');

 Inserting values into customer table values

INSERT INTO CUSTOMER VALUES ('C00012', 'tribhuvan', 'visakhapatnam', 'vizag', 'india', '2', '6000.00', '5000.00', '7000.00', '4000.00', '9898989898', 'A003');

INSERT INTO CUSTOMER VALUES ('C00021', 'vinod', 'rajahmundry', 'rjy', 'india', '2', '3000.00', '5000.00', '2000.00', '6000.00', '7878787878', 'A008');

INSERT INTO CUSTOMER VALUES ('C00027', 'kalyan', 'hyderabad', 'hyd', 'india', '3', '5000.00', '7000.00', '6000.00', '6000.00', '9400940021', 'A008');

 Inserting values into orders table values

INSERT INTO ORDERS VALUES('200102', '1000.00', '600.00', '08/01/2008', 'C00013', 'A003', 'SOD');

INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04/15/2008', 'C00019', 'A006', 'SOD');

INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08/30/2008', 'C00007', 'A009', 'SOD');

Creating user

create user abhiram identified by abhi123;

grant create session to abhiram;

create user manasa identified by manu123;

grant create session to manasa;

Creating Roles &Assigning Roles

create role cust_serv_clerk;

grant select on customer,agents,orders, to cust_serv_clerk;

grant select,insert,update on customer to cust_serv_clerk;

grant insert,update,delete on customer,agents,orders to cust_serv_mgr;

create role cust_serv_mgr;

grant cust_serv_clerk to cust_serv_mgr;

Assiginging Roles to the Particular User 

grant cust_serv_clerk to abhiram

grant cust_serv_mgr to manasa;

create user chaitanya identified by chaitu123

default tablespace users

quota 10m on users

temporary tablespace temp

quota 5m on system

profile application_user

password expire;

Note: Info on USER MANAGEMENT IN ORACLE,This is for the practical purpose how to assign a role and profile and resources to the user to a database it may be differ from your environment

like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME

 

   

 

Oracle Database Cloning Using Rman Utility

Oracle Database Cloning Using Rman Utility

-----------------------------------------------------

Prerequisites 

-------------

1)OEL Oracle enterprise Linux server

2)Oracle installed with out database

Activity Flow

------------- 

1)Take source backup using Rman

2)move pfile,Controlfile,backup pieces, to target server 

3)Start the Instance in Mount Stage and Restore from backup pieces

4)Open the database as source SID

5)Rename the Database

Trigger Backup on source

------------------------

$ RMAN> backup database plus archivelog delete input;

$ RMAN> restore controlfile to '/tmp/prod_control.ctl';

Move files to Target server

----------------------------

parameter file pfile

edit pfile change SID except for DB_NAME parameter keep it source

create directories as per new pfile

$ RMAN> rman target /catalog rman-rc/rman-rc@rca

$ RMAN>backup database plus archivelog delete input;

$ RMAN>list backup of database summary;

$ RMAN>restore controlfile to '/tmp/prod_control.ctl';

$ RMAN>exit

$ cd ORACLE_HOME/dbs

$ ls -lrt

  initproddb.ora

 

i want to copy the initproddb.ora to the target server using scp

$ scp initproddb.ora oracle@192.168.0.100:$ORACLE_HOME/dbs    (it will ask password promt enter the password remote target server and enter it)

initproddb.ora   (you will prompt the 100% complete)

Target server

-------------

$ cd   ORACLE_HOME/dbs

$ ls -lrth

initproddb.ora

open this file in VI editor

replace with 

%s/proddb/testdb/   

save and exit the file  

(source db is proddb and target db is testdb create the directories as per new file)

(after that open the parameter file initproddb.ora in cat command we need to create directories)

$ cat initproddb.ora

$ mkdir -p /u01/app/oracle/admin/testdb/adump

$ mkdir -p /u01/app/oracle/oradata/testdb/

$ mkdir -p /u01/app/oracle/fast_recovery_area/testdb/

$ mkdir -p /u01/app/oracle/fast_recovery_area

scp/tmp/prod_control.ctl ---> target server control location  move files to target server

$ cd /tmp

 $ ls -lrth

source server

----------------

$ scp prod_control.ctl oracle@ 192.168.0.100:/u01/app/oracle/oradata/testdb/control01.ctl

$ scp prod_control.ctl oracle@ 192.168.0.100:/u01/app/oracle/oradata/testdb/control02.ctl

Target Server

-------------

$ ls -lrth

$ /u01/app/oracle/oradata/testdb/control01.ctl

$ /u01/app/oracle/oradata/testdb/control02.ctl

database backup pieces ---> same location as source

$ rman target /catalog rman -rc/rman_rc@rcat

conneceted target database :proddb (DBID=674237234)

connecteed to recovery catalog database

$ RMAN> list backup of database summary;  (it will show like this key ty lv device type and tag)

key --> 2949  ty--->B   LV--->A  Device type---> disk  Tag ---> TAG202012t105306  (tag is the important using tag only we can perform restore and recovery in backup)

$ RMAN> list backup TAG202012t105306;

list of backup piece name :/u01/app/oracle/fast_receovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp

$ scp/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp oracle@192.168.0.100:/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/

Target server

--------------

$ mkdir-p /u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20   (target server directory may not exist create this directory)

archive backup pieces ---> same location as source

$ scp/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp oracle@192.168.0.100:/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/

Start cloning 

------------------

export environment variables connect to rman

Target Server

-------------

$ env |grep ora

 ORACLE_SID =proddb

ORACLE_HOME= '/u01/app/oracle/product/11.2.0/db_home-1

$ rman target /

connected to target database (not started)

$ RMAN> startup mount;

get the last scn available in the archive log backup

$RMAN> list backup of archivelog all;

last archive log--> 6  next scn---.> 960034

-Rename the DB redolog files so they can be created in new location

--------------------------------------------------------------------

sql> alter database rename file '/u01/app/oracle/oradata/proddb/redo01.log' to '/u01/app/oracle/oradata/testdb/redo01.log';

Target server

-------------

sql> select member from v$logfle;

member

-------

/u01/app/oracle/oradata/proddb/redo03.log

/u01/app/oracle/oradata/proddb/redo02.log

/u01/app/oracle/oradata/proddb/redo01.log

we have to change the datafile proddb to testdb redo03,02,01

Restore the datafiles to new location

----------------------------------------

run {

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/system01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/sysaux01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/undotbs01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/user01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/example01.dbf';

restore datafile from TAG202012t105306;

switch datafile all;

recover database untill scn 960034;

alter database open resetlogs;

}

Renaming Database after cloning

--------------------------------

sql> select name,open_mode from v$database;

name--->proddb  open_mode->read_write 

hostname  dctest.chaitanya.com

 ( we have to rename the proddb to testdb we are in the testserver)

Take control file backup to trace with resetlog options

--------------------------------------------------------

sqlplus '/as sysdba'

sql> alter database backup controlfile to trace as'/tmp/ create_ctrol_file.sql';

sql> database altered

sql> shut immedaite ;

sql>exit

create pfile for new dbid

-------------------------

$cd $ORACLE_HOME/dbs

$ ls -ltr

initproddb.ora

$ mv initproddb.ora inittestdb.ora

$ vi inittestdb.ora        (one parameter db_name =proddb change to testdb   db_name=testdb save and exit vi editor)

$ export ORACLE_SID= testdb 

sqlplus '/as sysdba'

startup instance in nomount stage

---------------------------------

sql>startup nomount;

exit

$ cd/tmp

ls -lrt

create_ctrolfile.sql           (copy the create_ctrolfile.sql to new note pad and paste it)

$ cat create_ctrolfile.sql

Edit the control file in trace location with new sid  

-----------------------------------------------------

create control file set database  "testdb" resetlogs archivelog

remove reuse and set change norestlogs to resetlogs

it look like this when u open file 

maxlogfile

--

---

---

log file

--

--

group1 /u01---

group2 /u01---

group3 /u01---

datafile 

 

------/u01

---

---

---

---

character set WE8MSWIN1252

$cd  $ ORACLE_HOME/dbs  (remove the oldcontrol file)

$ ls -lrt

$ inittestdb.ora

cat inittsetdb.ora

$ rm-rf /u01/app/oracle/oradata/testdb/control01.ctl /u01/app/oracle/oradata/testdb/control02.ctl

Create controlfiles for new instance

------------------------------------

sqlplus '/as sysdba'

sql> select instance_name ,status from v%instance;

instance_name -->testdb   

status--->started

we have created control statement create_ctrol.sql and run 

 it will display controlfile created

sql> alter database open resetlogs

sql>select name,open_mode from v$database;

name---> testdb

open_mode---> read,write

sql> select name from v$ controlfile;

sql>select member from v$ logfile; 

cloning is done

Note: it will differ in your environment it may be u r development or testing or production and also directory structure in linux  mountpoints and IP address but the process is same.  

THANK YOU FOR VIEWING FOR MORE UPDATES VISIT MY BLOG 

http://chaitanyaoracledba.blogspot.com/ 

Diagnostic Files in Oracle

Diagnostic Files in Oracle

Introduction:

 In this blog i am going to explain Diagnostic Files in Oracle To troubleshoot the database issues /errors/events/messages/alerts for that we have diagnostic files from 11g oracle introduced centralized location with the parameter

diagnostic_dest

sql>show parameter_dest   (it will dispaly the location)

  /u01/app/oracle/diag/rdbms/prod/prod/trace

bdump - background_dump_dest

udump - user_dump_dest

cdump - core_dump_dest  

$ cd /u01/app/oracle/diag/rdbms/prod/prod/trace

$ ls-ltr *.log

  In Diag folder contains Diagnostic Files in Oracle 

  1)Alert log
  2)Trace Files

Alert log :  format alert_SID.log (SID is the System Identifier in database name/instance name

 for instance prod

 eg: alert_prod.log

This alert log contains the following content

1)parameter changes

2)startup/shutdown

3)tablespace /datafiles creation /add with time stamp

 ORA -1555 snapshot too old
 ORA -0600 internal error

q)How to read alert log file in using in unix tail command

A) $ tail -100f alert_prod.log  (will read the last 100 lines while reading the tail the message will append)

 Trace Files :
 

For Every Background process in the event of error/failure will generate a trace file with process name and unix process id(PID) in Diagnostic Files in Oracle 

 $ ls -ltr *.trc    (.trc is the extension of trace file)

 the loaction of the trace file at the event will append in alert log file

 $more prod_arc2_5809.trc
 eg: found error : ORA-12541: TNS: NO LISTENER

TASKS

• Hosekeeping alert/tracefiles
• 
  
• we maintain months for retention for alert/trace files
• 
  
• monitoring alert log file with tail command
• 
  
• monitoring table space usage and adding data files
• 
  
• verifying instance status
• 
  
• database is up and running 
• 
  
• parameter changes
• 
  

Steps:


1)Take backup using CP command in unix

2)Truncate with cat command in unix

3)Zip the backup file in backup location

a) $ cd/u01/app/oracle/diag/rdbms/Prod/prod/trace

b)$ ls -lrth *.log (find the current size of the log file)

c)$ cp alert_prod.log /orabackup/alert_prod.log200720

d)$ cat/dev/null >alert_prod.log

e)$ cd /orabackup

f)$ ls -lrt

g)$ gzip alert_prod.log200720  (the file is zipped)

e) ls -lrth (we can check the zipped files and size)


NOTE:  if alert file is removed what happens ? operation will still continue no impact on instance ,in the event the new alert log will get create

NOTE: Info on Diagnostic Files in Oracle it may be differ in your environment like production,testing,development etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME

Complete Oracle SQL Blog for Developers and Administrators

COMPLETE ORACLE SQL  BLOG FOR DEVELOPERS AND ADMINISTRATORS 

Introduction:

 In this Blog i am going to explain  Complete  Oracle SQL Blog for Developers and Administrators, short for Structured Query Language is pronounced Ess Queue el and is a simple non procedural language that lets you store and retrieve data in a relational database.

Data types:

Data type means the format in which we have to store a particular value in a field.

The main data types in oracle are

 

1.     char(n):

 

Fixed-length character data (string), n characters long. The maximum size for n is 255 bytes(2000 in Oracle8). Note that a string of type char is always padded on right with blanks to full length of n. (+ can be memory consuming).

 

Example: char(40)

 

2.     varchar2 (n):

 

Variable-length character string. The maximum size for n is 2000. Only the bytes used

for a string require storage.

 

Example: varchar2 (80)

 

3.     number (o, d):

 

Numeric data type for integers and real. o = overall number of digits,

d= number of digits to the right of the decimal point.

Maximum values: o =38, d= −84 to +127.

 

 Examples: number (8), number (5, 2)

 

Note that, e.g., number (5, 2) cannot contain anything larger than 999.99

without resulting in an error. Data types derived from number are integer, decimal, small int and real.

 

4.     Date:

 

 Date data type for storing date and time. The default format for date is: DD-MMM-YY.

 

  Examples: ’13-OCT-94’, ’07-JAN-98’

Languages in SQL:

 

SQL consists of 5 languages.

1. Data Definition Language.

2. Data Manipulation Language.

3. Data Control Language.

4. Data Retrieval Language.

5. Transaction Control Language.

 

1.     Data Definition Language:

 

Data Definition Language (DDL) that is used to define the structural

characteristics of your databases. The following statements create or remove databases and tables or modify the structure of tables:

 

· create database creates a new database.

· drop database removes a database and any tables it contains.

· create table creates a new table.

· drop table removes a table and any data it contains.

· alter table modifies the structure of an existing table.

 

Creating a table:

create table <tablename>(attribute-name datatype (size), attribute-name datatype (size),“ “ );

 

To view the structure of a table:

 

Syn: desc <table name>;

 

To see all the table names in a particular database:

 

Syn: select * from tab;

 

Dropping a table:

 

Syn: drop table <table name>;

 

2.     Data Manipulation Language:

 

After a table has been created using the create table command,

tuples can be inserted into the table, or tuples can be deleted or modified

a. Insert:-

The most simple way to insert a tuple into a table is to use the insert statement

 

Syn: insert into <table> [(<column i. . . column j>)]

Values (<value i. . . value j>);

Ex: insert into project

(pno, pname, persons, budget, pstart) values (313, ’dbs’, 4, 150000.42, ’10-oct-94’);

or

insert into project values (313, ’dbs’, 7411, null, 150000.42, ’10-oct-94’, null);

 

b. Update:

For modifying attribute values of (some) tuples in a table, we use the update statement.

Syn:

Update <table> set <column i> = <expression i>. . . <column j> = <expression j>[where

<condition>];

Ex:

update emp set job = ’manager’, deptno = 20, sal = sal +1000 where ename = ‘chaitanya’;

 

c. Delete:

All or selected tuples can be deleted from a table using the delete command:

Syn:

delete from <table> [where <condition>];

Ex: delete from PROJECT where PEND < sysdate>;

 

3.     Data Control Language:

 

A Data Control Language (DCL) is a computer language and a subset of

SQL, used to control access to data in a database.

Examples of DCL commands include:

 

 grant to allow specified users to perform specified tasks.

 revoke to cancel previously granted or denied permissions.

 

4.     Data Retrieval Language:

 

This language is used to retrieve the data from the table in the database .The command

under this category is “select”.

Syn:

Select * from <table name>;

 

 

5.     Transaction Control Language:

 

A Transaction Control Language (TCL) is a compute language and a subset of SQL, used

to control transactional processing in a database. Examples of TCL commands include:

 commit to apply the transaction.

 rollback to undo all changes of a transaction.

 savepoint to divide the transaction into smaller sections.

 

CONSTRAINTS

 

Constraint:

 

Constraints are mainly used to restrict the table under certain conditions.

Several types of Oracle constraints can be applied to Oracle tables to enforce

data integrity, including:

 

· Oracle "Check" Constraint:

 

This constraint validates incoming columns at row insert time.

For example, rather than having an application verify that all occurrences of region are North,South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.

 

· Not Null Constraint:

 

This Oracle constraint is used to specify that a column may never contain

a NULL value. This is enforced at SQL insert and update time.

Syntax for creating a not null constraint at the time of creation Of a table:

 

Create table <table name> (attribute name data type(size) not null);

Syntax for creating a not null constraint after creation of a table:

Alter table <table name> modify (<attribute name> data type (size) not null);

 

DROPPING A CONSTRAINT:

 

Alter table <table name> drop constraint <constraint name>;

 

DISABLE A CONSTRAINT:

 

Alter table <table name> disable constraint <constraint name>;

 

ENABLE A CONSTRAINT:

 

Alter table <table name> enable constraint <constraint name>;

 

· Primary Key Constraint:

 

This Oracle constraint is used to identify the primary key for a table.

This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.

 

Syntax for creating a primary key constraint at the time of creation Of a table:

Create table <table name> (attribute name data type (size) primary key);

 

Syntax for creating a primary key constraint after creation of a table:

Alter table <table name> add primary key (attribute name1, attribute name2);

 

DROPPING A CONSTRAINT:

 

Alter table <table name> drop constraint <constraint name>;

 

DISABLE A CONSTRAINT:

 

Alter table <table name> disable constraint <constraint name>;

 

ENABLE A CONSTRAINT:

 

Alter table <table name> enable constraint <constraint name>;

 

References Constraint:

 

This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL INSERT and DELETE times. At SQL DELETE time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.

 

Syntax for creating a Foreign key constraint after creation of a table:

alter table (table name) add constraint (foreign key constraint name) foreign key ( field name )

references primary_table_name (primary_table_primary_index_field);

 

DROPPING A CONSTRAINT:

 

Alter table <table name> drop constraint <constraint name>;

 

DISABLE A CONSTRAINT:

 

Alter table <table name> disable constraint <constraint name>;

 

ENABLE A CONSTRAINT:

 

Alter table <table name> enable constraint <constraint name>;

 

· Unique Constraint:

 

This Oracle constraint is used to ensure that all column values within a

table never contain a duplicate entry.

 

Syntax for creating a unique constraint at the time of creation Of a table:

Create table <table name> (attribute name data type (size) unique);

Syntax for creating a unique constraint after creation of a table:

Alter table <table name> add unique (attribute name1, attribute name2);

 

DROPPING A CONSTRAINT:

 

Alter table <table name> drop constraint <constraint name>;

 

DISABLE A CONSTRAINT:

 

Alter table <table name> disable constraint <constraint name>;

 

ENABLE A CONSTRAINT:

 

Alter table <table name> enable constraint <constraint name>;

 

DEFAULT:--

This command is used to set default values for an attribute i.e, whenever the user enters any value into that default attribute then the value which is entered will be there in the table. If the user doesn’t enter any value into that default attribute.., then the default value will be present in the table.

 

Syntax for creating a table using default option…

create table <table name>(<att. name> data type(size) default ‘value’);

 

eg. create table student(sno number(5),sname varchar2(20),gender char(10) default ‘male’, or name varchar2(20) default ‘CHAITANYA’);

The value should be in uppercase letters only…

 

Syntax using alter after creation creation of table…

Alter table <tablename> modify(<att.name> datatype(size) default ‘VALUE’, <att.name>

datatype(size) default ‘VALUE’);

 

Alter table student modify(gender char(10) default ‘MALE’, orname varchar2(20) default ‘CHAITANYA’);

It wont disturb the present value in the table,It sets the default value where the value is null in the table.

 

TO CHANGE THE DEFAULT VALUE,

Alter table student modify(sno number(5) default 10);

 

TO REMOVE DEFAULT VALUE,

Alter table student modify(sno number(5) default ‘’);

 

FLASH BACK:

This command is used to bring the dropped tables again into the data base. All the table which are

dropped in the database will be stored in a oracle predefined table called as “recycle bin”. We can get back the dropped table unless and until that table is present in the recycle bin.. oracle allows the users to clear the dropped tables from the recycle bin.

 

Syntax for bringing the dropped table from the database

flashback table <tablename> to before drop;

flashback table student to before drop;

 

Syntax for viewing all the dropped tables in the database

show recylebin or

select * from recyclebin;

 

Syntax for removing all the tables from the recyclebin

purge recyclebin;

 

Syntax for removing particular tables from the recyclebin

purge table <tablename>

purge table student;

 

Syntax for renaming a table at the time of bringing from recyclebin

flashback table <tablename> to before drop rename to <new tablename>;

flashback table student to before drop rename to student1;

 

Syntax for removing a table permanently from the database without going into the recyclebin

drop table <tablename> purge;

drop table student purge;

 

COUNT:

This command is used to display the total count of numbers of records present in a table.

select count(*) from <tablename>;

select count(sno) from <tablename>;

select count(*),count(sno) from student;

 

ROWNUMBER:

Every record inserted into a table will be having a rownumber.By using this row number,

we can display particular range of records in a table.the operators which are applicable for rownumbers is <,<= and =(only for first row)

The operators which are not applicable for rownumber are >,>= and =(other than first row)

q) want to display 1st five records in a table??

select * from <tablename> where rownum<=5;

 

ROWID:

Rowid means whenever the user inserts one record into a table,then oracle by default

creates one unique rowid for that row…this rowid is 16 digit hexa value.a user can differentiate “n” no.of records by using the rowid….

Syntax to see the rowid value for a particular record

selelct rowid <att. name> from <tablename>;

Syntax to delete record for a particular table

delete from <tablename> where rowid-“_______________”;

 

DISTINCT:

 

This command is used to display non repeated values in a table.

select distinct(sname) from student;

select distinct(att.name) from <tablename>;

select distinct sno,sname from student;

 

ORDER BY:

 

This command is used to display the records of a particular table in either ascending order or in descending order of particular attribute…By default,It will display in ascending order…

q) want to display all the records of student table in ascending order of their marks??

Select * from student order by marks;

q) want to display all the records of student table in descending order of their marks??

Select * from student order by marks desc;

 

OPERATORS:

 

logical and,or,not

 

select * from emp where salary=5000 and ename=’CHAITANTA’;

select * from emp where salary =5000 or ename=’CHAITANYA’;

select * from emp where not salary=5000;

 

ARITHEMATIC +,-,*,/ 

 

update emp  set salary=salary+5000;

update emp  set salary=salary-5000;

 

MISCLENEOUS

 

between,not between,is,is not,in,not in,like,not like between/not between

 

select * from emp where salary between 2000 and 5000;

select * from emp where salary not between 2000 and 5000;

 

IS/IS NOT

 

select * from student where sno is null;

select * from student where sname is null;

select * from student where sno = ‘ ’;

select * from student where sno is not null;

select * from student where sno< >’’;

 

IN/NOT IN

 

select * from student where sno in(1,2,4);

select * from student where sno not in(1,2,4);

 

LIKE/NOT LIKE

 

select * from tab where tname like ‘n%’;

it display the tables which starts with n

 

select * from tab where tname like ‘%n’;

it display the tables which ends with n

 

select * from tab where tname like ‘_%n’;

 it display the tables which sum characters ends with n

 

select * from tab where tname like ‘_ _ _’;

it display the tables which are having 3 characters

 

select * from tab where tname not like ‘n%’;

it display the tables which not starting with n

 

select * from tab where tname like ‘%abc%’;

it display the tables which starts with some character and ends with other and having abc in the middle.

 

ALIAS:

 

This command is used to give alias names for a particular attribute…

select sname as “chaitanya” from student;

It display the column names with alias name but not stores with that alias name.

 

We can also write as follows

 

select sname “chaitanya” from student;

select sname chaitanya from student;

 

CONCATENATION:

 

This command is used to append two strings together. the symbol used to concatenate is ||

select ‘my name is ‘||ename||’ with eno ‘||eno||’ earning a salary of ‘||salary from emp;

 

DECODE:

 

Decode is used to differentiate between any two types of things and display the appropriate one. It works as an if condition in C language.

 

select decode(gender,’m’,’mr’,’miss’) from emp;

It will display MR in decode if the gender is M otherwise it will show MISS

 

CASE:

 

This operator is used to call dynamically a particular value in that attribute and display

another name.

 

select sname,case when ‘abc’ then ‘name staring with a’

when ‘pqr’ then ‘name staring with p’

else ‘name not staring with a and p’

end from emp;

 

ALL & ANY:

 

ALL:

 

IT display the records of a table which satisfies all the conditions in the given query.

 

select * from student where sno>all(3,4);

 

ANY:

 

It displays the records of a particular table which satisfies anyone condition in the given

query.

 

select * from student where sno>any(3,4);

 

FUNCTIONS

 

FUNCTIONS are classified into 4 types.

 

1.date functions

2.string functions

3.mathematical functions

4.aggregate functions

 

Date Functions:

 

Dual:-- Dual is a dummy table which will be created in the database when oracle is loaded.

 

q) how to see today’s date??

 

select sysdate from dual;

 

q) how to display today’s date along with time??

 

select systimestamp from dual;

 

add_months:

 

This function is used to add ‘n’ no. of months to the current date or any date. This function is also used to subtract n no.of months to the given date.

 

select add_months(jdate,6) from emp;

select add_months(sysdate,6) from dual;

select add_months(sysdate,-6) from dual;

 

months_between:

 

This function is used to display the difference between n no. of months between two dates.

 

select months_between(sysdate,jdate) from emp;

select round(months_between(sysdate,jdate) from emp;

 

last_day:

 

This function is used to display the last day of the given months.

 

select last_day(sysdate) from dual;

select last_day(’01-feb-10’) from dual;

 

next_day:

 

This function is used to display the next week’s date.

 

select next_day(sysdate,’mon’) from dual;

this will display the first upcoming Monday

 

select next_day(sysdate+3,’mon’) from dual;

This will display the next week Monday date…

formats in date functions

 

d - no. of day in a week

dd - no. of day in a month

ddd - no. of day in a year

day - full name of the day in a week

dy - it will show 3 letters of day in a week

mon - 3 letters of the month in a year

month - full name of month in a year

mm - no of month in a year

q - no of quarter in which the month is existing

y - last digit of the year

yy - last 2 digits of the year

yyy - last 3 digits of the year

yyyy - last 4 digits of the year

hh - no of hours in 12 digit format

hh24 - no of hours in 24 digit format

mi - no. of minites

ss - no of seconds

sp - spelling of given month no,date no,year no

th - suffix of date

spth - spelling with suffix

w - no of week in the month

ww - no of week in the year

 

the 2 date conversion functions which are used in oracle are to_char and to_date.

To_char:

 

this function is used to convert the standard date format values into the user defined date format

select to_char(jdate,’dd/mm/yy’) from emp;

select to_char(‘jdate,’d’) from emp;

select to_char(‘jdate,’dd’) from emp;

select to_char(‘jdate,’ddd’) from emp;

select to_char(‘jdate,’day’) from emp;

select to_char(‘jdate,’mm’) from emp;

select to_char(‘jdate,’mon’) from emp;

select to_char(‘jdate,’q’) from emp;

select to_char(‘jdate,’y’) from emp;

select to_char(‘jdate,’yy’) from emp;

select to_char(‘jdate,’yyy’) from emp;

select to_char(‘jdate,’yyyy’) from emp;

select to_char(‘jdate,’hh’) from emp;

select to_char(‘jdate,’hh24’) from emp;

select to_char(‘jdate,’mi’) from emp;

select to_char(‘jdate,’ss’) from emp;

select to_char(‘jdate,’w’) from emp;

select to_char(‘jdate,’ww’) from emp;

select to_char(‘jdate,’ddsp’) from emp;

select to_char(‘jdate,’ddth’) from emp;

 

TO_DATE:

 

This function is used to convert the user defined date into system defined date and it again converts the system defined date into the user defined date format..

 

select to_char(to_date(’31-dec-10’),’dd/mm/yy’) from dual;

 

GREATEST AND LEAST

 

GREATEST:

 

This function is used to display the greatest date among the given dates.

select greatest(to_date(’16-jan-09’),to_date(’17-jul-09’)) from dual;

o/p:- 17-jul-09

 

select greatest(’16-jan-09’,’17-jul-09’) from dual;

o/p:- 17-jul-09

 

LEAST:

 

This function is used to display the least date among the given dates.

select least(to_date(’16-jan-09’),to_date(’17-jul-09’)) from dual;

o/p:- 16-jan-09

 

select least(’16-jan-09’,’17-jul-09’) from dual;

o/p:- 16-jan-09

 

STRING FUNCTIONS:

 

LENGTH:

 

This function is used to display the total length of a given string.

select length(‘chaitanya’) from dual;

select length(sname),sname from student;

select length(fname||sname) from student;

 

INITCAP:

 

This function is used to display the first letter in the word as a capital letter

select initcap(‘chaitanya chakravarthy divakala’) from dual;

O/P : Chaitanya Chakravarthy Divakala

UPPER:

 

This function is used to convert the lower case letters of the given string into upper case

letters.

 

select upper(‘chaitanya’) from dual;

o/p:  CHAITANYA

 

LOWER:

 

This function is used to convert the upper case letters of given string into lower case letters

select lower(‘CHAITANYA’) from dual;

o/p : Chaitanya

 

ASCII:

 

This function is used to convert the given character into corresponding ascii codes.

select ascii(‘a’) from dual;

o/p:  65

 

CHR:

 

This function is used to convert the given ascii codes into corresponding character.

select chr(65) from dual;

o/p : a

 

LPAD:

 

This function is used to add a string to left side ‘n’ no. of times to the given length of the

string.

select lpad(sname,10,”*”) from student;

select lpad(‘chaitanya’,10,”*”) from dual;

select lpad(sname,10,”*$”) from student;

 

RPAD:

 

This function is used to add a string to right side ‘n’ no. of times to the given length of the string.

select rpad(sname,10,”*”) from student;

select rpad(‘chaitanya’,10,”*”) from dual;

select rpad(sname,10,”*$”) from student;

 

SOUNDEX:

 

 This function is used to display all the records which will sound with the similar name.

select * from student where soundex(sname)=soundex(‘chaitanya’);

 

TRIM:

 

Trim consists of 3 types

 

1.ltrim

2.rtrim

3.trim

 

1.LTRIM:

 

This function is used to remove the required characters from the left side.

select ltrim(‘chaitanya’,’c’) from dual;

o/p: haitanya

 

select ltrim(‘chaitanya’.’cha’) from dual;

o/p: itanya

select ltrim(‘chaitanya’,’ch’) from dual;

o/p :aitanya

 

2.RTRIM:

 

This function is used to remove the required characters from the right side.

select rtrim(‘chaitanya’,’c’) from dual;

o/p: chaitanya

select rtrim(‘chaitanya’.’nya’) from dual;

o/p:chaita

select rtrim(‘chaitanya’,’tanya’) from dual;

o/p:chai

 

TRIM:

 

Both ltrim and rtrim will trim by character wise but not by string wise…….

 

 It consists of 3 types

 

1.leading

2.trailing

3.both

 

1.LEADING:

 

This function is used to remove a character from left side

select trim(leading ‘c’ from ‘chaitanya’) from dual;

o/p: haitanya

2.TRAILING:

 

 This function is used to remove a character from right side

select trim(trailing ‘a’ from ‘chaitanya’) from dual;

o/p: chaitany

 

3.BOTH:

 

This function is used to remove a character from both sides

select trim(both ‘m’ from ‘manasa’) from dual;

o/p: anasa

select trim( ‘m’ from ‘manasa’) from dual;

o/p: anasa

 

TRANSLATE:

 

 This function is used to translate the letters in the given string with the user defined

letters. this function is letter based.

 

select translate(‘dccvizag’,’dvg’,’abc’) from dual;

o/p: accbizac

 

select translate('yayay','y','k') from dual;

o/p: kakak

 

REPLACE:

 

 This function is used to replace a particular word in the given string with the

User defined word. this function is strictly a word based.

 

select replace(‘dccvizag’,’dcc’,’hyd’)from dual;

o/p:hydvizag

 

NVL:

 

 This function is used to display the null values in an attribute with the userdefined data..

select nvl(sname,'dcc') from student;

 

NVL2:

 

This function is used to display the names in an attribute with one userdefined name and the null values in the same attribute with another user defined name.

select nvl2(sname,'vsp','hyd') from student;

 

INSTR:

 

 This function is used to return the position of a particular character in a string.

select instr('visakhapatnam','a') from dual;

o/p: 4

it also used to return the position of a character not only from the beginning...,

select instr('visakhapatnam','a',5) from dual;

o/p: 7

 

SUBSTR:

 

 This function is used to return the string from any postion upto end of the string.

select substr('viskahapatnam',4) from dual;

o/p: akhapatnam

This function is also used to display the string from mth position to nth position

select substr('visakhapatnam',4,7) from dual;

o/p: akhapat

 

ARTHEMATIC FUNCTIONS

 

ABS:

 

 This function is used to convert all the negative numbers into positive numbers and display on the screen

select abs(15) from dual; 15

select abs(-15) from dual; 15

 

CEIL:

 

 This function is used to display the next value

select ceil(12.2) from dual; 13

select ceil(12.7) from dual; 13

select ceil(12.275) from dual; 13

 

FLOOR:

 This function is used to display the same value

select floor(12.2) from dual; 12

select floor(12.7) from dual; 12

 

ROUND:

 This function is used to round the value to 'n' no.of positions

select round(12.257,2) from dual; 12.26

select round(12.72,1) from dual; 12.7

select round(12.247,1) from dual; 12.2

 

TRUNC:

This function is used to cut the given number to some user defined number.

select trunc(12.757,2) from dual; 12.75

select trunc(12.72,1) from dual; 12.7

 

MOD:

 This function is used to display the remainder between two numbers.

select mod(5,2) from dual; 1

select mod(2,5) from dual; 2

 

POWER:

This function is used to display the power of a given number.

select power(5,2) from dual; 25

 

SQRT:

 This function is used to display the square root of a given number.

select sqrt(81) from dual; 9

 

AGGREGATE FUNCTIONS

 

MAX:

 This function is used to display the maximum salary or the highest value of a particular

attribute.

select max(sal) from emp;

 

MIN:

 This function is used to display the minimum salary or the lowest value of a particular attribute.

select min(sal) from emp;

 

AVG:

 This function is used to display the average salary

select avg(sal) from emp;

 

SUM:

 This function is used to display the total salary

select sum(sal) from emp;

 

GROUPBY:

 

guidelines for using group functions

 

• distinct makes the function consider only nonduplicate values; all makes it consider every

value including duplicates. the default is all and therefore does not need to be specified.

• the data types for the functions with an expr argument may be char, varchar2, number, or date.

• all group functions ignore null values. to substitute a value for null values, use the nvl, nvl2, or coalesce functions.

• the oracle server implicitly sorts the result set in ascending order when using a group by clause.

to override this default ordering, desc can be used in an order by clause.

 

HAVING:

 

The  having clause to specify which groups are to be displayed, and thus, you further restrict the groups on the basis of aggregate information.

in the syntax:

group_condition restricts the groups of rows returned to those groups for which

specified condition is true the oracle server performs the following steps when you use the having clause:

 

1. rows are grouped.

2. the group function is applied to the group.

3. the groups that match the criteria in the having clause are displayed.

the having clause can precede the group by clause, but it is recommended that you place the groupby

clause first because that is more logical. groups are formed and group functions are calculated before

the having clause is applied to the groups in the select list.

 

VIEWS:

 

A view is a virtual table defined by a query. it provides a mechanism to create alternate

ways of working with the data in a database. a view acts much like a table. we can query it with a select, and some views even allow insert, update,and delete.

however, a view doesn’t have any data. all of its data are ultimately derived from tables all of its data are ultimately derived from tables like those we created in views are similar to derived tables, except that views are de.ned once and can be used in many queries.

 

syntax: create a view using the create view command.

create view <view name> [(<column list>)] as <select statement>

this creates a view named <view name>. the column names/types and data for the view

are determined by the result table derived by executing <select statement>.

let’s create a view showing the ingredients (ingredient id, inventory, and inventory value) supplied to us by veggies_r_us.

example:

create view vrs as select ingredientid, name, inventory, inventory * unitprice as value from ingredients

i, vendors v where i.vendorid = v.vendorid and companyname = 'veggies_r_us';

Note: this views may contain expressions and even simple literals.

display: vrs

ingredientid name inventory value

letus lettuce 200 2.00

pickl pickle 800 32.00

tomto tomato 15 0.45

 

Note :  A view’s select statement may refer to other views. because the view is just a virtual table, any changes to the base tables are instantly reflected in the view data.

example:update the tomato inventory

update ingredients

set inventory = inventory * 2

where ingredientid = 'tomto';

 

why to use a view ?

views have several uses

 

1)usability

2)security

3)reduced dependency

 

updating views:

 

standard for a view to be updatable, not contain distinct

1)not reference the same column twice in the select clause

2)not have a group by or having clause

3)not contain union, except, or intersect

4)contain attributes from only one table

5)have exactly one row in a base table that corresponds to each row in the view

updating through views :

update vrs set inventory = inventory * 2;

inserting through view:

insert into vrs(ingredientid, name, inventory) values 'newin','new ingredient',100);

drop view

drop view <view name> [cascade | restrict]

 

Advantages of views

 

• restrict database access

• simplify queries

• provide data independence

• provide multiple views of the same data

• can be removed without affecting the underlying data

view options

• can be a simple view, based on one table

• can be a complex view based on more than one table or view can contain groups of functions

• can replace other views with the same name

• can contain a check constraint

• can be read-only

SYNONYMS:

 

Synonyms are used to create a duplicate copy of a table.while creating a synonyms there is no possibility to use a where condition.

it cannot capture the result of any sql query in the synonyms which can be possible in views.

 

create a synonyms:

 

syn: create synonyms <synonym name> for <table name>;

eg: create synonym emp_syn for emp;

drop a synonyms:

syn:drop synonym <synonym name>;

eg: drop synonym emp_syn;

 

SEQUENCES:

 

Sequence is an independent object and used with in a table that required for generating

numbers in either assending or desending order. oracle provides an object as sequence and providing intervels between the numbers.In a table maximum information required for generating a number.simillarly minimum information required for generating numbers using sequencemust be a starting number.increment value for generating the next number.

 

Creating a sequence:

 

create sequence <sequence name>

start with <value>

increment by <value>

max value<value>

min value <value>

cycle

order

cache;

eg: create sequence chaitanya_seq start with 200

increment by 2

max value 450

min value 200

cycle;

sequence created

confirming a sequence:verify your sequence values in the

user_sequences data dictionary table.

 

Note : verify a sequence value in user_sequence

the last_number column displays the next available sequence number if nocache is specified.

 

Rules for using n extval and currval:

 

The select list of a subquery in an insert statement

2)select list of a select statement that is not part of a subquery

1)the select list of a view

2)a select statement with group by, having, or order by clauses and with the distinctkeyword

views the current value for sequence:

select dept_deptid_seq.currval from dual;

o/p:- currval

---------

120

 

Caching sequence values:

 

Cache sequences in memory to provide faster access to those sequence values,cache is populated the first cache is populated the first each request for the next sequence value is retrieved from the cached

viewing the next available sequence value without incrementing it if the sequence was created with nocache, it is possible to view the next available sequence value

without incrementing it by querying the user_sequences table.

 

Altering a sequence:

 

modify it by using the alter sequence statement.

syntax:

alter sequence sequence

increment by n

{maxvalue n | nomaxvalue}

{minvalue n | nominvalue}

{cycle | nocycle}

{cache n | nocache};

eg:

alter sequence dept_deptid_seq

increment by 20

maxvalue 90

nocache

nocycle;

Removing a sequence:-

to remove a sequence from the data dictionary, use the drop sequence statement.

syntax: drop sequence <sequence name>;

 

INDEXES:

 

An oracle server index is a schema object that can speed up the retrieval of rows by using a pointer.indexes can be created explicitly or automatically.  an index provides direct and fast access to rows in a table. its purpose is to reduce the necessity of disk i/oby using an indexed path to locate data quickly. the index is used and maintained

automatically by the oracle server. once an index is created, no direct activity is required by the user.

 

indexes are logically and physically independent of the table they index. It is created or dropped at any time and have no effect on the base tables or other indexes.

how are index created a unique index is created automatically when you define

a primary key or unique constraint in a table definition.oracle allows us the creation of two types

 

1) duplicate index

2) unique index

 

creation of duplicate index : an index created on single attibute of a table is called as simple index.it

accepts duplicate values for the indexed column.

(simple )

syn: create index <index name> on <table name>

<column-name>

eg: create index emp_ind on emp(sno);

 

Composite index:

 

An index created on more than one attibute of a table is called as composite index.it accepts

duplicate values for the indexed column.

syn: create index <index name> on <table name> <column name>.<column name>............;

eg: create index emp_index on emp(sno,sname...);

 

Creating of simple unique:

 

syn : create unique index <index name> on <table name> (<column name>);

eg: create unique index emp_index on emp (sno);

Creating of composite unique index:-

syn:

create unique index <index name> on <tablename>(<colunn name><column name>........);

eg:create unique index emp_index on emp(sno,sname.........);

Drop a index:-

syn :drop index <index name>;

ex: drop index emp_index;

View the contents of the index:-

1)by using user_index we can view the index name and table name,but it cannot view the

column names on which the index is placed

2)by using user_ ind_ columns we can view the columns on which the index is crated.

Disable the index:-

syn : alter index <indexname> unuable;

ex: alter index emp_ind unusable;

Enabling the index:-

syn: alter index <index name> enable;

ex: alter index emp_ind enable;

 

Note : Info on Complete  Oracle SQL Blog for Developers and Administrators it may be differ in your environment like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME