Tag Archives: create

Oracle

Recreating OEM in 11g on MS Windows

Leave a comment

There are multiple reasons to recreate your Enterprise Manager installation on a Oracle RDBMS system. The following procedures provide the steps to accomplish this task on a MS Windows OS.

1. Remove SYSMAN from database if he exists by using emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -deconfig dbcontrol db -repos drop

EXAMPLE:
—————————————
C:\Windows\system32>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 20, 2013 10:38:10 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:38:43 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_38_10.log.
Apr 20, 2013 10:38:45 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 20, 2013 10:39:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Apr 20, 2013 10:40:29 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:40:30 AM

C:\Windows\system32>
——————————————-

2. Create OEM repository by using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -repos create

EXAMPLE:
——————————————-
C:\Windows\system32>emca -repos create

STARTED EMCA at Apr 20, 2013 10:42:48 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYS user: password

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:43:12 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_42_48.log.
Apr 20, 2013 10:43:14 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 20, 2013 10:45:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:45:30 AM

C:\Windows\system32>
——————————————-

3. Finally, configure EM Grid Control using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -config dbcontrol db

EXAMPLE:
——————————————-
C:\Windows\system32>emca -config dbcontrol db

STARTED EMCA at Apr 20, 2013 10:32:32 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_orcl ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl

Local hostname ……………. orcl_SERVER-PC.CATT.Net
Listener ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl
Listener port number ……………. 1521
Database SID ……………. orcl1
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:33:00 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_32_31.log.
Apr 20, 2013 10:33:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepo
sitory
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 20, 2013 10:33:41 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 20, 2013 10:33:43 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://orcl_SERVER-PC.CATT.Net:5501/em < <<<<<<<<<< Apr 20, 2013 10:34:38 AM oracle.sysman.emcp.EMDBPostConfig invoke WARNING: ************************ WARNING ************************ Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: C:/orac le/product/11.2.0/dbhome_orcl/orcl_SERVER-PC.CATT.Net_orcl/sysman/config/emkey. ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. *********************************************************** Enterprise Manager configuration completed successfully FINISHED EMCA at Apr 20, 2013 10:34:38 AM C:\Windows\system32>
——————————————-

4. Now attempt to access the EM console through the URL given in the INFO section of the last command. In this example, we access the EM console at URL:
https://orcl_SERVER-PC.CATT.Net:5501/em

Larry Catt, OCP

11g, ASM, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, unix

Oracle DBCA – Database Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s DBCA (Database Configuration Assistant) is the GUI tool used to create starter databases within the Oracle RDBMS. The DBCA utility was introduced in Oracle 8i and provides the user a GUI to eliminate the need for manual configuration of database creation scripts. In this article we will review the startup of the Database Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.

mylinux:> ./dbca

6. The Welcome screen will display and the wizard from here on is pretty self explanatory. The DBCA utility provides you the ability to Create Databases, Configure Database Options, Delete Databases, Manage Database Templates, and Configure ASM instances for storage.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreating Oracle Public Database Link

Leave a comment

Oracle provides database links to create direct connections from one database instance to another. This feature can come in handy in the movement of small to moderate amounts of data between systems. In this article we will review the creation of the DDL to regenerate public database links defined within an Oracle database.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 3 19:21:09 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create a public database link from your database to another database to demonstrate generation of your DDL.

create public database link oracle11
connect to ljcatt identified by ljcatt
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)’;

SQL> create public database link oracle11
2 connect to ljcatt identified by ljcatt
3 using ‘(DESCRIPTION =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
5 (CONNECT_DATA =
6 (SERVER = DEDICATED)
7 (SERVICE_NAME = orcl11g)
8 )
9 )’;

Database link created.

SQL>

3. Execute the following PL/SQL block to generate your DB Links DDL.

set serveroutput on
declare

v_pwd varchar2(30);

begin

for rec in(select * from dba_db_links where owner=’PUBLIC’)
loop

dbms_output.put_line(‘create public database link ‘||rec.db_link);
dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(password)’);
dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);

end loop;
end;
/

SQL> set serveroutput on
SQL> declare
2
3 v_pwd varchar2(30);
4
5 begin
6
7 for rec in(select * from dba_db_links where owner=’PUBLIC’)
8 loop
9
10
11 dbms_output.put_line(‘create public database link ‘||rec.db_link);
12 dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(pass
word)’);
13 dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);
14
15
16 end loop;
17 end;
18 /
create public database link ORACLE11.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to LJCATT identified by (password)
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT =
1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)

)
)’;

PL/SQL procedure successfully completed.

SQL>

4. Now you can use the DDL generated to move or recreate your public database link. NOTE: In Oracle 9i you can retrieve the password through the view sys.link$. However, in Oracle 10g and on, all passwords are encrypted, thus you must manually insert the actual password.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Recreate Oracle Sequence

Leave a comment

Oracle uses Sequences to generate unique numbers to identify specific records. Once a sequence has been defined all a programmer or Oracle developer needs to obtain a unique key value for a record is to call the sequence. This article demonstrates how to generate the DDL for an already defined sequences in an Oracle database.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 18 12:18:49 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the sequences TEST_SEQ and TEST_SEQ2 under the schema LJCATT to demonstrate generation of the sequences DDL.

SQL> connect ljcatt/ljcatt
Connected.
SQL> create sequence test_seq start with 1 increment by 1 nocache;

Sequence created.

SQL> create sequence test_seq2 start with 10 increment by 10 cache 10;

Sequence created.

SQL>

3. Execute the following PL/SQL block to regenerate the DDL for the test sequence numbers.

/******************************************************
//**
//** Larry Catt
//** Recreation of sequences.
//**
//** Oracle 8 to Oracle 11g.
//**
//******************************************************/

set serveroutput on
declare

ct number;
v_cycle varchar2(10);
v_order varchar2(10);
v_owner varchar2(30):=’LJCATT’;

begin

for seq in(select * from dba_sequences where sequence_owner=v_owner)
loop

ct :=seq.last_number+100;

if seq.cycle_flag = ‘N’ then
v_cycle :=’nocycle’;
else
v_cycle :=’cycle’;
end if;

if seq.order_flag = ‘N’ then
v_order :=’noorder’;
else
v_order :=’order’;
end if;

if seq.cache_size>0 then

dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequence_name||’ start with
‘||
ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxvalue
‘||seq.max_value||
‘ ‘||v_cycle||’ ‘|| v_order ||’ cache ‘||seq.cache_size||’;’);
else
dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequence_name||’ start with
‘||
ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxvalue
‘||seq.max_value||
‘ ‘||v_cycle||’ ‘|| v_order ||’ nocache;’);
end if;
end loop;
end;
/

SQL> set serveroutput on
SQL> declare
2
3 ct number;
4 v_cycle varchar2(10);
5 v_order varchar2(10);
6 v_owner varchar2(30):=’LJCATT’;
7
8 begin
9
10 for seq in(select * from dba_sequences where sequence_owner=v_owner)
11 loop
12
13 ct :=seq.last_number+100;
14
15 if seq.cycle_flag = ‘N’ then
16 v_cycle :=’nocycle’;
17 else
18 v_cycle :=’cycle’;
19 end if;
20
21 if seq.order_flag = ‘N’ then
22 v_order :=’noorder’;
23 else
24 v_order :=’order’;
25 end if;
26
27 if seq.cache_size>0 then
28
29 dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.seque
nce_name||’ start with
30 ‘||
31 ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxv
alue
32 ‘||seq.max_value||
33 ‘ ‘||v_cycle||’ ‘|| v_order ||’ cache ‘||seq.cache_size||’;’);
34 else
35 dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequ
ence_name||’ start with
36 ‘||
37 ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxv
alue
38 ‘||seq.max_value||
39 ‘ ‘||v_cycle||’ ‘|| v_order ||’ nocache;’);
40 end if;
41 end loop;
42 end;
43 /
create sequence LJCATT.TEST_SEQ2 start with
110 increment by 10 minvalue 1
maxvalue
999999999999999999999999999 nocycle noorder cache 10;
create sequence LJCATT.TEST_SEQ start with
101 increment by 1 minvalue 1
maxvalue
999999999999999999999999999 nocycle noorder nocache;

PL/SQL procedure successfully completed.

SQL>

4. You can now use the DDL to recreate the sequence.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Recreate Oracle Roles

Leave a comment

Oracle uses Roles to define the privileges and rights a user has in an Oracle database. By defining a Role with specific system and object privileges, we greatly reduce the complexity of creating a new user account within any system. This is accomplished by assigning all specific rights to a single Role then granting that Role to a new user, instead of granting all the rights individually to each new account. In this article we will review a script which will regenerate the DDL to recreate a specific role or just view the privileges which that role contains.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 05 17:42:43 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the role VIEW_ALL_SCOTT to demonstrate the generation of DDL.

Create role VIEW_ALL_SCOTT identified by password;
Grant connect to VIEW_ALL_SCOTT;
Grant create session to VIEW_ALL_SCOTT;
Grant select on scott.DEPT to VIEW_ALL_SCOTT;
Grant select on scott.EMP to VIEW_ALL_SCOTT;
Grant select on scott.BONUS to VIEW_ALL_SCOTT;
Grant select on scott.SALGRADE to VIEW_ALL_SCOTT;

3. Execute the following PL/SQL block to regenerate the DDL of the role VIEW_ALL_SCOTT.

set serveroutput on
spool ./create_role.sql

declare

v_role varchar2(30):= ‘VIEW_ALL_SCOTT’;
v_ct number;

begin

— Create original role definition

dbms_output.put_line(‘create role ‘||v_role||’ identified by
‘||v_role||’;’);

— Grant all roles defined for the role.

for role in(select * from dba_role_privs where grantee=v_role)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

— Grant all system privileges for the role.

for sys_priv in(select * from dba_sys_privs where grantee=v_role)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

— Grant all object privileges for the role.

for tab_priv in(select * from dba_tab_privs where grantee=v_role)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

SQL> spool ./create_role.sql
SQL>
SQL> declare
2
3 v_role varchar2(30):= ‘VIEW_ALL_SCOTT’;
4 v_ct number;
5
6 begin
7
8 — Create original role definition
9
10 dbms_output.put_line(‘create role ‘||v_role||’ identified by
11 ‘||v_role||’;’);
12
13
14
15 — Grant all roles defined for the role.
16
17 for role in(select * from dba_role_privs where grantee=v_role)
18 loop
19 if role.admin_option = ‘YES’
20 then
21 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ w
ith
22 admin option’||’;’);
23 else
24 dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’
);
25 end if;
26 end loop;
27
28
29 — Grant all system privileges for the role.
30
31 for sys_priv in(select * from dba_sys_privs where grantee=v_role)
32 loop
33 if sys_priv.admin_option = ‘YES’
34 then
35 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’
36 with admin option’||’;’);
37 else
38 dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
39 ‘||sys_priv.grantee||’;’);
40 end if;
41 end loop;
42
43 — Grant all object privileges for the role.
44
45 for tab_priv in(select * from dba_tab_privs where grantee=v_role)
46 loop
47 if tab_priv.grantable = ‘YES’
48 then
49 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
50 ‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ w
ith
51 grant option;’);
52 else
53 dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
54 ‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’)
;
55 end if;
56 end loop;
57 end;
58 /
create role VIEW_ALL_SCOTT identified by VIEW_ALL_SCOTT;
grant CONNECT to VIEW_ALL_SCOTT;
grant CREATE SESSION to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.DEPT to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.EMP to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.BONUS to VIEW_ALL_SCOTT;
grant SELECT on SCOTT.SALGRADE to VIEW_ALL_SCOTT;

PL/SQL procedure successfully completed.

SQL>

4. Now you can use the DDL generated to recreate the role in another database or examine the DDL to find the specific rights a user will receive once they are granted the role.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Recreate Oracle RDBMS User accounts

Leave a comment

With the advent of Oracle 10g Import and Export data pump utility, the need to recreate a user account from scratch has been greatly reduced. However, there are still situation when you may need the SQL to recreate a specific user within another Oracle environment or just desire to see the creation statements. The script below will generate the statements to create the user defined in the CURR variable from the current Oracle RDBMS environment.

1. First we will create a user named LJCATT with various roles and privileges.

SQL> create user LJCATT identified by LJCATT;
User created.
SQL> alter user LJCATT default tablespace USERS;
User altered.
SQL> alter user LJCATT temporary tablespace TEMP;
User altered.
SQL> alter user LJCATT profile DEFAULT;
User altered.
SQL> grant DBA to LJCATT;
Grant succeeded.
SQL> grant CREATE TABLE to LJCATT with admin option;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to LJCATT;
Grant succeeded.
SQL> grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
Grant succeeded.
SQL>

2. Now we will execute the following PL/SQL block to regenerate the statements to recreate the user from the data dictionary.

set serveroutput on
spool ./create_user_account.sql

declare

curr varchar2(30):= ‘LJCATT’;
v_ext varchar2(3);

begin

— Create original user definition
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);
if user.account_status<>‘OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;
end loop;

— Grant all roles defined for the user.

for role in(select * from dba_role_privs where grantee=curr)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

— Grant all system privileges for the user.

for sys_priv in(select * from dba_sys_privs where grantee=curr)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

— Grant all object privileges for the user.

for tab_priv in(select * from dba_tab_privs where grantee=curr)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

spool off

Sample Output results:

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
create user LJCATT identified by LJCATT;
alter user LJCATT default tablespace USERS;
alter user LJCATT temporary tablespace TEMP;
alter user LJCATT profile DEFAULT;
grant DBA to LJCATT;
grant CREATE TABLE to LJCATT with admin option;
grant UNLIMITED TABLESPACE to LJCATT;
grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;

PL/SQL procedure successfully completed.

SQL>

3. The above SQL statements can now be used against any other Oracle database to recreate the user account with the same rights which exist in this database. NOTE: There exist several dependencies in the above statements, such as: tablespaces, profiles, roles, and object privileges. For example, we need to take care that a default tablespace defined in the alter user statement truly exist in the destination database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Setup Automatic Oracle STATSPACK jobs

Leave a comment

Oracle provides the package DBMS_JOB(Pre 10g) and DBMS_SCHEDULER(Post 10g) to allow for the automation of pre-scheduled routine tasks in the Oracle database, just as they are performed by CRONTAB on UNIX and the Scheduler on Windows. This article will explain the use of DBMS_JOB and DBMS_SCHEDULER to take automatic snapshots for the STATSPACK utility every two hours. It is important to realize that STATSPACK stores information related to system performance in the database, thus the more often you take snapshots, the great resources you will be consuming.

1. The DBMS_JOB package contains several procedures and functions for creation, modification and deletion of scheduled jobs for Oracle 9i and earlier versions. For Oracle 10g and higher versions use DBMS_SCHEDULER package, which provides for greater flexibility and control over job creation. A complete listing of the procedures and functions can be retrieved in SQL*PLUS with the command desc for both packages.

2. Use the SUBMIT procedure to create a new job to execute. The below statement will execute once every two hours.

For 9i or earlier use dbms_job.submit.

variable jobno number;

exec dbms_job.submit(job=>:jobno, what=>’begin statspack.snap;
end;’,next_date=>sysdate,interval=>’sysdate+1/12′);

For 10g or higher use dbms_scheduler.create_job (NOTE: start_date is a timestamp not a date value, so use the following query to determine the timestamp format for your database (

Oracle, Oracle 10g, oracle 9i, SQL

Recreate Oracle View

Leave a comment

Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a view’s DDL for review or transport.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.

SQL> grant create view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

View created.

SQL>

3. Execute the following PL/SQL block to generate your DDL.

set serveroutput on
declare

v_ct number;
v_owner varchar2(30):=’SCOTT’;

begin

for v_view in(select view_name, text from dba_views where owner=v_owner)
loop

dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);

end loop;
end;
/

SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;

PL/SQL procedure successfully completed.

SQL>
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate the Oracle DDL for a PROFILE

Leave a comment

Oracle uses PROFILES to limit the access and resources a user has within an Oracle database. In a properly secured Oracle environment care must be taken in defining resource limits of your users. This article covers the creation of the DDL to define an Oracle profile already established in one database and recreate it in another.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 19 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the profile TEST_PROFILE to demonstrate the recreation of the DDL to generate the profile in another database.

SQL> create profile test_profile limit IDLE_TIME 15
2 CONNECT_TIME 30
3 FAILED_LOGIN_ATTEMPTS 3
4 PASSWORD_LIFE_TIME 5
5 PASSWORD_REUSE_TIME 3
6 PASSWORD_REUSE_MAX 3
7 PASSWORD_LOCK_TIME 30
8 PASSWORD_GRACE_TIME 30
9 SESSIONS_PER_USER 2;

Profile created.

SQL>

3. Execute the following PL/SQL block to recreate the DDL for your profile.

set serveroutput on
spool ./create_profile.lst

declare

v_ct number;
v_profile varchar2(30):=’TEST_PROFILE’;
begin

dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);

for v_limit in(select resource_name, limit from dba_profiles
where profile = v_profile)
loop

dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);

end loop;
end;
/

SQL> set serveroutput on
SQL>
SQL> spool ./create_profile.lst
SQL>
SQL> declare
2
3 v_ct number;
4 v_profile varchar2(30):=’TEST_PROFILE’;
5 begin
6
7 dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);
8
9 for v_limit in(select resource_name, limit from dba_profiles
10 where profile = v_profile)
11 loop
12
13 dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);
14
15 end loop;
16 end;
17 /
create profile TEST_PROFILE limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 15
CONNECT_TIME 30
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 5
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 30

PL/SQL procedure successfully completed.

SQL>

4. With the DDL generated you can recreate the TEST_PROFILE in any other Oracle database environment.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, script, SQL, sqlplus, unix

Creating an external file from within the Oracle RDBMS.

Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will show a basic procedure for the producing an OS layer file from within the Oracle RDBMS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 03 09:43:17 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Ensure that the system initialization parameter for utl_file_dir contains the directory to which utl_file is going to write with the command show utl_file_dir.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string

SQL>

5. If it does not contain the directory, as is the case in step 4, logon to your Oracle database as sysdba and add the directory structure and bounce the database, as seen below:

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————

utl_file_dir string /home/lcatt

SQL>

6. Execute the newly create procedure with the SQL*PLUS command; execute test_file

SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

7. Exit out of SQL*PLUS and change directory to where you wrote your file.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Mylinux:/home/lcatt:>cd /home/lcatt
Mylinux:/home/lcatt:>

8. Execute the ls and more command on the file testfile.

Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>more testfile
Testing file creation
Mylinux:/home/lcatt:>

This completes the creation of external OS layer file from within the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com