Login as oracle user
The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 9i instances on Linux, but it works equally well for Oracle 10g, 11G and 12c also. It can be used on any RHEL-style distribution, including Oracle Linux, up to an including RHEL6.
Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to 'Y'.
TSH1:/u01/app/oracle/product/9.2.0:Y
ORACLE_HOSTNAME = ora11g.home.com with your own host name and
ORACLE_SID=ora11g with your own SID name which you have entered at the time of oracle installation
Create Script files
Login as root user
Next, create a file called /etc/init.d/dbora as the root user, containing the following.
This method can still be used under Oracle 10g, 11g and 12c, provided the "ORA_HOME" variable is amended to use the correct path and this is added to the end of the dbstart and dbshut lines. The lines to start and stop the listener can be removed under Oracle 10g Release 2 onward, as the dbstart command includes an automatic start of the listener.
#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
#ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
#ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
#ORA_HOME=/u01/app/oracle/product/12.1.0/db_1
ORA_OWNER=oracle
export ORACLE_UNQNAME=orcl
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
esacUse the chmod command to set the privileges to 750.
chmod 750 /etc/init.d/dbora
Create symbolic links to the dbora script in the appropriate run-level script directories as follows.
ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
Next, we must create the "startup.sh" and "shutdown.sh" scripts in the "/home/oracle/scripts". First create the directory.
mkdir -p /home/oracle/scripts
chown oracle.oinstall /home/oracle/scripts
The /home/oracle/scripts/startup.sh script should contain the following commands.
#!/bin/bash export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/oracle #export ORACLE_HOSTNAME=ol6-112.localdomain #export ORACLE_UNQNAME=DB11G export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_HOSTNAME=localhost.localdomain export ORACLE_UNQNAME=orcl #export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1 export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH export ORACLE_SID=db12c ORAENV_ASK=NO . oraenv ORAENV_ASK=YES # Start Listener lsnrctl start # Start Database sqlplus / as sysdba << EOF STARTUP; EXIT; EOF
The /home/oracle/scripts/shutdown.sh script is similar.
#!/bin/bash export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/oracle #export ORACLE_UNQNAME=DB11G #export ORACLE_HOSTNAME=ol6-112.localdomain export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_HOSTNAME=localhost.localdomain export ORACLE_UNQNAME=orcl export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1 export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH export ORACLE_SID=db12c ORAENV_ASK=NO . oraenv ORAENV_ASK=YES # Stop Database sqlplus / as sysdba << EOF SHUTDOWN IMMEDIATE; EXIT; EOF # Stop Listener lsnrctl stop
Note. You could move the environment settings into the "dbora" file or into a separate file that is sourced in the startup and shutdown script. I kept it local to the script so you could see the type of things that need to be set in case you have to write a script to deal with multiple installations, instances and listeners.
Make sure the permissions and ownership of the files is correct.
chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
chown oracle.oinstall /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
Replace host name with IP address in tnsnames.ora and listner.ora files.
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
Solution:
SQL>alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.240)(PORT=1521))";
SQL>alter system register;
Restart your system and you will see the listener and database will now start and stop automatically with the machine. You may test as follows:
login as: oracle
oracle@192.168.1.200's password:
Last login: Wed Feb 6 05:10:13 2013 from 192.168.1.141
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 05:24:31 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
In Oracle 11g a new parameter called SEC_CASE_SENSITIVE_LOGON which is defaults to TRUE for case sensitive passwords. My SCOTT password was "tiger" is small case and Forms 10g was taking it as "TIGER" on runtime.
Login "AS SYSDBA" in sqlplus.
Solution 1:
- Connect as sys and change the parameter to false.
SQL> alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;
Otherwise following error will occured.
ORA-01017 Invalid username/password logon denied
Connecting form 6i to oracle database 10G/ express edition/11gR2 32 bit
changed the database character set to UTF8, i show may step maybe will help for anybody.
Login "AS SYSDBA" in sqlplus.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;
--Pre-Import
[oracle@prod ~]$ mkdir /u01/dumps
[oracle@prod ~]$ ls /u01/
app database dumps
[oracle@prod ~]$
Login "AS SYSDBA" in sqlplus.
SQL> create tablespace has_tablespace datafile 'has.dat' size 20M autoextend on;
If not properly created then drop as:
DROP TABLESPACE has_tablespace1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE has_tablespace1
INCLUDING CONTENTS
CASCADE CONSTRAINTS;
SQL>DROP TABLESPACE has_tablespace INCLUDING CONTENTS AND DATAFILES;
Login as has/shserver@dbname
If you find below error
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Solution:
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.240)(PORT=1521))";
SQL> alter system register;
SQL> create or replace directory export_dp as '/u01/dumps/';
SQL> create or replace directory import_dp as '/u01/dumps/';
SQL> SELECT * FROM dba_directories;If directories not properly created then drop them as:
SQL> drop directory import_dp;
SQL> drop directory export_dp;
Before import confirm login of user as userid/password@dbname
[oracle@prod ~]$ impdp has/shserver@royal directory=import_dp dumpfile=royal-26112024_215032.dmp logfile=log_has.log REMAP_SCHEMA=sys:has
--Post Import
Auto Backup of Dump File
LINUX
Login as Root user
Write shell script - create file expdp.sh in oracle home and place it in cd /home/oracle/scripts/
[root@localhost ~]# cd /home/oracle/scripts/
[root@localhost scripts]#
[root@localhost scripts]# vi expdp.sh
Copy and Paste below lines.
#!/bin/ksh
#Script to perform datapump export every hour
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=sr
export PATH=$PATH:$ORACLE_HOME/bin
expdp has/shserver@sr directory=export_dp dumpfile=sr-$(date +%d%m%Y_%H%M%S).dmp logfile=sr-$(date +%d%m%Y_%H%M%S).log schemas=has
#mv /tmp/shisb*.dmp /u01/dumps/
#mv /tmp/shisb*.log /u01/dumps/
#chmod o+r /u01/dumps/*
# chmod u+x /home/oracle/scripts/expdp.sh
# chown oracle.oinstall /home/oracle/scripts/expdp.sh
and add following lines
# daily logical export
01 23 * * * /home/oracle/scripts/expdp.sh
save and quite
RMAN Backup if required as per storage capacity of server
[oracle@ora1 ~]$ sqlplus / as sysdba
SQL> archive log list;
SQL> show parameter recovery_file_dest;
SQL> alter system set db_recovery_file_dest_size=75g scope=both;
SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
SQL> exit;
[oracle@ora1 ~]$ rman target /;
RMAN> backup database plus archivelog;
To fix this error ORA-39213: Metadata processing is not available
Symptoms: You get the following error during expdp or impdp ORA-39006: internal error
Solution: Execute sys.dbms_metadata_util.load_stylesheets to fix this problem
PL/SQL procedure successfully completed. |
[oracle@localhost ~]$ crontab -e
and add following lines
# daily logical export
01 23 * * * /home/oracle/scripts/expdp.sh
save and quite
For more
Windows
Create batch file exp.bat with following lines.
Append Date & time to File name in Window Batch file
Sometimes it may be required to schedule the same task twice a days . In such cases, we have to create two batch file and scheduling them separately because the generated name may overwrites or throws errors . To overcome from this issue , we can schedule the same task with single batch file. Suppose we have to take the logical backup of a schema(say scott) twice a days . We can do this by simply appending the date and time to the dump file name .
As in case of Linux /Unix systems, shell scripting is very liberal with variables and we can define according to ourself . e.g;
expdate=`date ‘+%d%m%Y’`
dat=`date ‘+%m%d%y %H:%M:%S’`
And then go onto define in our script as
./expdp system/xxxx dumpfile=scott_$expdate.dmp logfile=scott_log_$expdate.log schemas=scott
But on windows machine ,we use the Date and Time function. The Date and Time function are as below :
Date: %date:~4,2%-%date:~7,2%-%date:~12,2%
Time: %time:~0,2%-%time:~3,2%-%time:~6,2%
This above function can be use to generate the unique dumpfile name. Below is the Demo of this function .
c:\> exp system/ramtech@noida owner=scott file=c:\scott_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp log=c:\scottlog_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~3,2%-%time:~6,2%.log
The file name generated i.e, dump file name is 'scott_12-22-11-12-04-31.dmp' and log file name is 'scottlog_12-22-11-04-31.log' where date is 12-22-11(12th-dec-2011) and time is 12-04-31(12hr:4min:31sec) .
exp myhotel1/myhotel1@xe owner=oadmin file=d:\myhotel1\backup\myhotel1_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp log=d:\myhotel1\backup\myhotel1log_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~3,2%-%time:~6,2%.log
exp oadm/nopassword@injsys file=c:\accounts\backup\----------------------------------
start > run > task
add task scheduler > ----------------------------------
ORACLE 11gR2 does not export (exp) empty tables
Oracle data pump is not always possible to use for export DB dumps. When you are working on a client machine and do not have access to server machine, still you need to use exp command.
In Oracle11gR2 does not export empty tables by the exp export utility. So that, tables will not be export at all, even the DDL definitions.
This problem is related to the new Oracle 11gR2 feature called deferred segment creation and the fact that empty tables are not listed in dba_segments.
This database feature is controlled by the database parameter deferred_segment_creation. This parameter is in default TRUE. If you set this value FALSE, it will export new empty tables those created after the parameter change.
Using following command you will able to export existing empty tables.
ALTER TABLE <empy table> ALLOCATE EXTENT;
export selected tables in oracle
exp username/password@database tables=table1,table2 file='c:\backup\table_data.dmp'
Login as has/shserver
--Enable/Disable Triggers if blanck dump imported
SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS;
TRIGGER_NAME
------------------------------
DSHIFT_TRG
KOTF_AUDIT_TRG
KOTD_AUDIT_TRG
KOTH_AUDIT_TRG
GSUBLDGR_AUDIT_TRG
PICKDROP_TRG
COMPBOX_TRG
PARKING_TRG
KOTF_TRG
KOTH_TRG
VRD_TRG
TRIGGER_NAME
------------------------------
STAY_TRG
GSUBLDGR_TRG
CASHDEPOSIT_TRG
14 rows selected.
alter trigger DSHIFT_TRG disable;
alter trigger KOTF_AUDIT_TRG disable;
alter trigger KOTD_AUDIT_TRG disable;
alter trigger KOTH_AUDIT_TRG disable;
alter trigger GSUBLDGR_AUDIT_TRG disable;
alter trigger PICKDROP_TRG disable;
alter trigger COMPBOX_TRG disable;
alter trigger PARKING_TRG disable;
alter trigger KOTF_TRG disable;
alter trigger KOTH_TRG disable;
alter trigger VRD_TRG disable;
alter trigger STAY_TRG disable;
alter trigger GSUBLDGR_TRG disable;
alter trigger CASHDEPOSIT_TRG disable;
update working set wopbaldr = 0, wopbalcr = 0;
delete from kotf;
delete from kotd;
delete from koth;
delete from stay;
delete from pick_drop;
delete from comp_box;
delete from parking;
delete from event_ord;
delete from guest_balancing;
delete from cash_deposit;
delete from gsubldgr;
delete from vrd;
delete from vrh;
delete from gsubldgr;
delete from kotf;
delete from kotpsch;
delete from kotd;
delete from koth;
delete from rcserv;
delete from stay_detail;
delete from stay;
delete from resv_master;
delete from dshift;
delete from mthtb where acpcode <> 5;
delete from working where acpcode <> 5;
delete from acperiods where acpcode <> 5;
delete from arledger;
delete from cotariff;
delete from COCSERVICE;
delete from company;
delete from roomno;
drop sequence resvmaster_idseq;
drop sequence stay_idseq;
drop sequence koth_idseq;
drop sequence kotd_idseq;
drop sequence kotf_idseq;
drop sequence gsubldgr_idseq;
drop sequence vrd_snoseq;
create sequence resvmaster_idseq start with 1 increment by 1;
create sequence stay_idseq start with 1 increment by 1;
create sequence koth_idseq start with 1 increment by 1;
create sequence kotd_idseq start with 1 increment by 1;
create sequence kotf_idseq start with 1 increment by 1;
create sequence gsubldgr_idseq start with 1 increment by 1;
create sequence vrd_snoseq start with 1 increment by 1;
delete from KOTF_AUDIT;
delete from KOTD_AUDIT;
delete from KOTH_AUDIT;
delete from GSUBLDGR_AUDIT;
drop table SYS_EXPORT_SCHEMA_01;
drop table menutype_temp;
drop table menu_temp;
--delete from menu;
--delete from menutype;
commit;
alter trigger DSHIFT_TRG enable;
alter trigger KOTF_AUDIT_TRG enable;
alter trigger KOTD_AUDIT_TRG enable;
alter trigger KOTH_AUDIT_TRG enable;
alter trigger GSUBLDGR_AUDIT_TRG enable;
alter trigger PICKDROP_TRG enable;
alter trigger COMPBOX_TRG enable;
alter trigger PARKING_TRG enable;
alter trigger KOTF_TRG enable;
alter trigger KOTH_TRG enable;
alter trigger VRD_TRG enable;
alter trigger STAY_TRG enable;
alter trigger GSUBLDGR_TRG enable;
alter trigger CASHDEPOSIT_TRG enable;
SQL> SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS;
--Create synonyms on sqlplus prompt
create public synonym acperiods for has.acperiods;
create public synonym arledger for has.arledger;
create public synonym cash_deposit for has.cash_deposit;
create public synonym city for has.city;
create public synonym cocservice for has.cocservice;
create public synonym company for has.company;
create public synonym compserv for has.compserv;
create public synonym cotariff for has.cotariff;
create public synonym country for has.country;
create public synonym co_chq_rec for has.co_chq_rec;
create public synonym currency for has.currency;
create public synonym dates for has.dates;
create public synonym dept for has.dept;
create public synonym dshift for has.dshift;
create public synonym emp for has.emp;
create public synonym event_ord for has.event_ord;
create public synonym gllevel for has.gllevel;
create public synonym gsubldgr for has.gsubldgr;
create public synonym gsubldgr_idseq for has.gsubldgr_idseq;
create public synonym guest_balancing for has.guest_balancing;
create public synonym guest_os_bal for has.guest_os_bal;
create public synonym kotd for has.kotd;
create public synonym kotd_idseq for has.kotd_idseq;
create public synonym kotf for has.kotf;
create public synonym kotf_idseq for has.kotf_idseq;
create public synonym koth for has.koth;
create public synonym koth_idseq for has.koth_idseq;
create public synonym kotpsch for has.kotpsch;
--create public synonym kot_co_view for has.kot_co_view;
--create public synonym kot_go_view for has.kot_go_view;
--create public synonym kot_po_view for has.kot_po_view;
create public synonym laundry for has.laundry;
create public synonym main for has.main;
create public synonym mdkot_view for has.mdkot_view;
create public synonym menu for has.menu;
create public synonym menutype for has.menutype;
create public synonym nation for has.nation;
create public synonym occup_view for has.occup_view;
create public synonym ordtype for has.ordtype;
create public synonym paymode for has.paymode;
create public synonym pls_expense for has.pls_expense;
create public synonym pls_sales for has.pls_sales;
create public synonym propinfo for has.propinfo;
create public synonym rcserv for has.rcserv;
create public synonym resvmaster_idseq for has.resvmaster_idseq;
create public synonym resvtype for has.resvtype;
create public synonym resv_master for has.resv_master;
create public synonym roomcat for has.roomcat;
create public synonym roomloc for has.roomloc;
create public synonym roomno for has.roomno;
create public synonym roomtype for has.roomtype;
create public synonym service for has.service;
create public synonym servtax for has.servtax;
create public synonym shift for has.shift;
create public synonym stay for has.stay;
create public synonym stay_idseq for has.stay_idseq;
create public synonym taxes for has.taxes;
create public synonym vrd for has.vrd;
create public synonym vrd_snoseq for has.vrd_snoseq;
create public synonym vrh for has.vrh;
create public synonym working for has.working;
create public synonym co_chq_rec for has.co_chq_rec;
create public synonym event_ord for has.event_ord;
create public synonym parking for has.parking;
create public synonym parking_trg for has.parking_trg;
create public synonym comp_box for has.comp_box;
create public synonym compbox_trg for has.compbox_trg;
create public synonym pick_drop for has.pick_drop;
create public synonym pickdrop_trg for has.pickdrop_trg;
create public synonym stay_detail for has.stay_detail;
create public synonym guest_os_bal for has.guest_os_bal;
create public synonym pls_sales for has.pls_sales;
create public synonym pls_expense for has.pls_expense;
create public synonym avail for has.avail;
create public synonym arledger for has.arledger;
create public synonym deals for has.deals;
create public synonym cotariff_deals_h for has.cotariff_deals_h;
create public synonym cotariff_deals_d for has.cotariff_deals_d;
create public synonym co_tariff_id_seq for has.co_tariff_id_seq;
create public synonym employeee for has.employeee;
create public synonym Emp_Salary for has.Emp_Salary;
create public synonym emp_Pic for has.emp_Pic;
create public synonym pc_ids for has.pc_ids;
--USER'S PRIVILEGES
--ROLES
--md
CREATE ROLE md_role;
GRANT CONNECT,RESOURCE TO md_role;
GRANT CREATE SESSION TO md_role;
GRANT EXECUTE ANY PROCEDURE TO md_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE, INSERT ANY TABLE TO md_role;
--CREATE USER muqeet identified by md007;
--GRANT md_role to muqeet;
--CREATE USER akhtar identified by swati;
--GRANT md_role to akhtar;
--fof_manager_role
CREATE ROLE fof_manager_role;
GRANT CONNECT,RESOURCE TO fof_manager_role;
GRANT CREATE SESSION TO fof_manager_role;
GRANT CREATE ANY VIEW TO fof_manager_role;
GRANT EXECUTE ANY PROCEDURE TO fof_manager_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON resv_master TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON stay TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON gsubldgr TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON acperiods TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON working TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON vrh TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON vrd TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON koth TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON kotd TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON kotf TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON kotpsch TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON laundry TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON dshift TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON cash_deposit TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON cocservice TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON company TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON compserv TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON cotariff TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON country TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON currency TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON dept TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON emp TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON nation TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON ordtype TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON paymode TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON rcserv TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON resvtype TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON roomcat TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON roomloc TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON roomno TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON roomtype TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON service TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON servtax TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON taxes TO fof_manager_role;
GRANT INSERT,UPDATE ON guest_balancing to fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON city TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON arledger TO fof_manager_role;
--GRANT update (ratetype) ON stay TO fof_manager_role;
GRANT INSERT,UPDATE ON co_chq_rec TO fof_manager_role;
GRANT INSERT,UPDATE ON event_ord TO fof_manager_role;
GRANT INSERT,UPDATE ON parking TO fof_manager_role;
GRANT INSERT,UPDATE ON comp_box TO fof_manager_role;
GRANT INSERT,UPDATE ON pick_drop TO fof_manager_role;
GRANT INSERT,UPDATE,DELETE ON stay_detail TO fof_manager_role;
GRANT SELECT ON avail TO fof_manager_role;
GRANT SELECT ON deals TO fof_manager_role;
GRANT SELECT ON cotariff_deals_h TO fof_manager_role;
GRANT SELECT ON cotariff_deals_d TO fof_manager_role;
--CREATE USER zaib identified by zaibswati;
--GRANT fof_manager_role to zaib;
--fof_assistant
CREATE ROLE fof_assistant_role;
GRANT CONNECT,RESOURCE TO fof_assistant_role;
GRANT CREATE SESSION TO fof_assistant_role;
GRANT CREATE ANY VIEW TO fof_assistant_role;
GRANT EXECUTE ANY PROCEDURE TO fof_assistant_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO fof_assistant_role;
GRANT INSERT,UPDATE ON resv_master TO fof_assistant_role;
GRANT INSERT,UPDATE ON stay TO fof_assistant_role;
GRANT INSERT ON gsubldgr TO fof_assistant_role;
GRANT INSERT ON vrh TO fof_assistant_role;
GRANT INSERT ON vrd TO fof_assistant_role;
GRANT INSERT,UPDATE ON koth TO fof_assistant_role;
GRANT INSERT,UPDATE ON kotd TO fof_assistant_role;
GRANT INSERT,UPDATE ON kotf TO fof_assistant_role;
GRANT INSERT,UPDATE ON kotpsch TO fof_assistant_role;
GRANT INSERT,UPDATE ON laundry TO fof_assistant_role;
GRANT INSERT ON dshift TO fof_assistant_role;
GRANT INSERT ON cash_deposit TO fof_assistant_role;
GRANT INSERT,UPDATE ON cocservice TO fof_assistant_role;
GRANT UPDATE ON company TO fof_assistant_role;
GRANT UPDATE ON compserv TO fof_assistant_role;
GRANT UPDATE ON cotariff TO fof_assistant_role;
--GRANT INSERT ON company TO fof_assistant_role;
--GRANT INSERT ON compserv TO fof_assistant_role;
--GRANT INSERT ON cotariff TO fof_assistant_role;
--GRANT INSERT ON country TO fof_assistant_role;
--GRANT INSERT ON currency TO fof_assistant_role;
--GRANT INSERT ON dept TO fof_assistant_role;
--GRANT INSERT ON emp TO fof_assistant_role;
--GRANT INSERT ON nation TO fof_assistant_role;
--GRANT INSERT ON ordtype TO fof_assistant_role;
--GRANT INSERT ON paymode TO fof_assistant_role;
GRANT INSERT ON rcserv TO fof_assistant_role;
--GRANT INSERT ON resvtype TO fof_assistant_role;
--GRANT INSERT ON roomcat TO fof_assistant_role;
--GRANT INSERT ON roomloc TO fof_assistant_role;
--GRANT INSERT ON roomno TO fof_assistant_role;
--GRANT INSERT ON roomtype TO fof_assistant_role;
--GRANT INSERT ON service TO fof_assistant_role;
--GRANT INSERT ON servtax TO fof_assistant_role;
--GRANT INSERT ON taxes TO fof_assistant_role;
GRANT INSERT,UPDATE ON cash_deposit TO fof_assistant_role;
GRANT INSERT,UPDATE ON guest_balancing to fof_assistant_role;
GRANT INSERT,UPDATE ON city TO fof_assistant_role;
GRANT INSERT,UPDATE ON dshift TO fof_assistant_role;
REVOKE UPDATE ON gsubldgr from fof_assistant_role;
--GRANT UPDATE ON gsubldgr TO fof_assistant_role;
GRANT INSERT,UPDATE ON vrh TO fof_assistant_role;
GRANT INSERT,UPDATE ON vrd TO fof_assistant_role;
GRANT INSERT,UPDATE,DELETE ON arledger TO fof_assistant_role;
--REVOKE update (ratetype) ON stay TO fof_assistant_role;
GRANT INSERT,UPDATE ON co_chq_rec TO fof_assistant_role;
GRANT INSERT,UPDATE ON event_ord TO fof_assistant_role;
GRANT INSERT,UPDATE ON parking TO fof_assistant_role;
GRANT INSERT,UPDATE ON comp_box TO fof_assistant_role;
GRANT INSERT,UPDATE ON pick_drop TO fof_assistant_role;
GRANT INSERT,UPDATE,DELETE ON stay_detail TO fof_assistant_role;
GRANT SELECT ON avail TO fof_assistant_role;
--REVOKE UPDATE ON resv_master from fof_assistant_role;
--REVOKE UPDATE ON stay from fof_assistant_role;
REVOKE UPDATE ON kotd FROM fof_assistant_role;
--CREATE USER sajjad identified by sajjad159;
--GRANT fof_assistant_role to sajjad;
--CREATE USER umar identified by umar357;
--GRANT fof_assistant_role to umar;
--CREATE USER shahid identified by shahid258;
--GRANT fof_assistant_role to shahid;
--fnb_manager
CREATE ROLE fnb_manager_role;
GRANT CONNECT,RESOURCE TO fnb_manager_role;
GRANT CREATE SESSION TO fnb_manager_role;
GRANT CREATE ANY VIEW TO fnb_manager_role;
GRANT EXECUTE ANY PROCEDURE TO fnb_manager_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO fnb_manager_role;
GRANT INSERT,UPDATE ON resv_master TO fnb_manager_role;
GRANT INSERT,UPDATE ON stay TO fnb_manager_role;
GRANT INSERT,UPDATE ON gsubldgr TO fnb_manager_role;
GRANT INSERT,UPDATE ON vrh TO fnb_manager_role;
GRANT INSERT,UPDATE ON vrd TO fnb_manager_role;
GRANT INSERT,UPDATE ON koth TO fnb_manager_role;
GRANT INSERT,UPDATE ON kotd TO fnb_manager_role;
GRANT INSERT,UPDATE ON kotf TO fnb_manager_role;
GRANT INSERT,UPDATE ON kotpsch TO fnb_manager_role;
GRANT INSERT,UPDATE ON emp TO fnb_manager_role;
GRANT INSERT,UPDATE ON ordtype TO fnb_manager_role;
GRANT INSERT,UPDATE ON menu TO fnb_manager_role;
GRANT INSERT,UPDATE ON menutype TO fnb_manager_role;
REVOKE UPDATE ON kotd FROM fnb_manager_role;
--CREATE USER adnan identified by adnan313 ;
--GRANT fnb_manager_role to adnan;
--CREATE USER kashif identified by kashif2200 ;
--GRANT fnb_manager_role to kashif;
--fnb_assistant
CREATE ROLE fnb_assistant_role;
GRANT CONNECT,RESOURCE TO fnb_assistant_role;
GRANT CREATE SESSION TO fnb_assistant_role;
GRANT CREATE ANY VIEW TO fnb_assistant_role;
GRANT EXECUTE ANY PROCEDURE TO fnb_assistant_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO fnb_assistant_role;
GRANT INSERT,UPDATE ON resv_master TO fnb_assistant_role;
GRANT INSERT,UPDATE ON stay TO fnb_assistant_role;
GRANT INSERT,UPDATE ON gsubldgr TO fnb_assistant_role;
GRANT INSERT,UPDATE ON vrh TO fnb_assistant_role;
GRANT INSERT,UPDATE ON vrd TO fnb_assistant_role;
GRANT INSERT,UPDATE ON koth TO fnb_assistant_role;
GRANT INSERT,UPDATE ON kotd TO fnb_assistant_role;
GRANT INSERT,UPDATE ON kotf TO fnb_assistant_role;
GRANT INSERT,UPDATE ON kotpsch TO fnb_assistant_role;
GRANT INSERT,UPDATE ON emp TO fnb_assistant_role;
GRANT INSERT,UPDATE ON ordtype TO fnb_assistant_role;
GRANT INSERT,UPDATE ON menu TO fnb_assistant_role;
GRANT INSERT,UPDATE ON menutype TO fnb_assistant_role;
REVOKE UPDATE ON resv_master FROM fnb_assistant_role;
REVOKE UPDATE ON stay FROM fnb_assistant_role;
REVOKE UPDATE ON gsubldgr FROM fnb_assistant_role;
REVOKE UPDATE ON vrh FROM fnb_assistant_role;
REVOKE UPDATE ON vrd FROM fnb_assistant_role;
REVOKE UPDATE ON koth FROM fnb_assistant_role;
REVOKE UPDATE ON kotd FROM fnb_assistant_role;
REVOKE UPDATE ON kotf FROM fnb_assistant_role;
REVOKE UPDATE ON kotpsch FROM fnb_assistant_role;
REVOKE UPDATE ON emp FROM fnb_assistant_role;
REVOKE UPDATE ON ordtype FROM fnb_assistant_role;
REVOKE UPDATE ON menu FROM fnb_assistant_role;
REVOKE UPDATE ON menutype FROM fnb_assistant_role;
--CREATE USER anjum identified by anjum147;
--GRANT fnb_assistant_role to anjum;
--acc_manager
CREATE ROLE acc_manager_role;
GRANT CONNECT,RESOURCE TO acc_manager_role;
GRANT CREATE SESSION TO acc_manager_role;
GRANT CREATE ANY VIEW TO acc_manager_role;
GRANT EXECUTE ANY PROCEDURE TO acc_manager_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON acperiods to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON gllevel to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON main to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON working to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON vrh to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON vrd to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON guest_balancing to acc_manager_role;
GRANT INSERT,UPDATE,DELETE ON arledger to acc_manager_role;
GRANT UPDATE ON company TO acc_manager_role;
GRANT UPDATE ON compserv TO acc_manager_role;
GRANT UPDATE ON cotariff TO acc_manager_role;
GRANT INSERT,UPDATE ON cocservice TO acc_manager_role;
--CREATE USER acctmgr identified by acctmgr369;
--GRANT acc_manager_role to acctmgr;
--acc_assistant
CREATE ROLE acc_assistant_role;
GRANT CONNECT,RESOURCE TO acc_assistant_role;
GRANT CREATE SESSION TO acc_assistant_role;
GRANT CREATE ANY VIEW TO acc_assistant_role;
GRANT EXECUTE ANY PROCEDURE TO acc_assistant_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO acc_assistant_role;
GRANT INSERT ON acperiods to acc_assistant_role;
GRANT INSERT ON gllevel to acc_assistant_role;
GRANT INSERT ON main to acc_assistant_role;
GRANT INSERT ON working to acc_assistant_role;
GRANT INSERT ON vrh to acc_assistant_role;
GRANT INSERT ON vrd to acc_assistant_role;
GRANT INSERT ON guest_balancing to acc_assistant_role;
--CREATE USER taimur identified by taimur69;
--GRANT acc_assistant_role to taimur;
--hk_manager
CREATE ROLE hk_manager_role;
GRANT CONNECT,RESOURCE TO hk_manager_role;
GRANT CREATE SESSION TO hk_manager_role;
GRANT CREATE ANY VIEW TO hk_manager_role;
GRANT EXECUTE ANY PROCEDURE TO hk_manager_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO hk_manager_role;
GRANT INSERT ON gsubldgr TO hk_manager_role;
GRANT INSERT,UPDATE,DELETE ON laundry TO hk_manager_role;
GRANT INSERT,UPDATE ON koth TO hk_manager_role;
GRANT INSERT,UPDATE ON kotd TO hk_manager_role;
GRANT INSERT,UPDATE ON kotf TO hk_manager_role;
GRANT INSERT,UPDATE ON vrh TO hk_manager_role;
GRANT INSERT,UPDATE ON vrd TO hk_manager_role;
REVOKE UPDATE ON kotd FROM hk_manager_role;
--CREATE USER hassan identified by hassan6644;
--GRANT hk_manager_role to hassan;
--CREATE USER mohsin identified by mohsin7979;
--GRANT hk_manager_role to mohsin;
--hk_assistant
CREATE ROLE hk_assistant_role;
GRANT CONNECT,RESOURCE TO hk_assistant_role;
GRANT CREATE SESSION TO hk_assistant_role;
GRANT CREATE ANY VIEW TO hk_assistant_role;
GRANT EXECUTE ANY PROCEDURE TO hk_assistant_role;
GRANT SELECT ANY TABLE, SELECT ANY SEQUENCE TO hk_assistant_role;
GRANT INSERT ON gsubldgr TO hk_assistant_role;
GRANT INSERT,UPDATE ON laundry TO hk_assistant_role;
GRANT INSERT,UPDATE ON koth TO hk_assistant_role;
GRANT INSERT,UPDATE ON kotd TO hk_assistant_role;
GRANT INSERT,UPDATE ON kotf TO hk_assistant_role;
GRANT INSERT,UPDATE ON vrh TO hk_assistant_role;
GRANT INSERT,UPDATE ON vrd TO hk_assistant_role;
REVOKE UPDATE ON kotd FROM hk_assistant_role;
No comments:
Post a Comment