Tag Archives: recreate

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

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, SQL

Create Oracle Public Synonyms

Leave a comment

Oracle uses synonyms to point users to other schema objects when the user does not have direct access to that schema. This is generally accomplished by creating a synonym owned by public to a private schema object. Once the public synonym is created all users have access to the private schema object. In this article we will recreate the DDL for public synonyms referencing one private schema’s objects.

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 15 02:57:41 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 public synonyms for all of the user SCOTT’s private tables.

Select ‘create public synonym ‘||table_name||’ for scott.’
||table_name||’;’ from dba_tables where owner=’SCOTT’;

SQL> Select ‘create public synonym ‘||table_name||’ for scott.’
2 ||table_name||’;’ from dba_tables where owner=’SCOTT’;

create public synonym DEPT for scott.DEPT;
create public synonym EMP for scott.EMP;
create public synonym BONUS for scott.BONUS;
create public synonym SALGRADE for scott.SALGRADE;

SQL> create public synonym DEPT for scott.DEPT;
Synonym created.

SQL> create public synonym EMP for scott.EMP;
Synonym created.

SQL> create public synonym BONUS for scott.BONUS;
Synonym created.

SQL> create public synonym SALGRADE for scott.SALGRADE;
Synonym created.

SQL>

3. Execute the following SQL statement to regenerate the DDL for public synonyms referring to private tables owned by the user SCOTT.

select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
from dba_synonyms where table_owner=’SCOTT’;

SQL> select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
2 from dba_synonyms where table_owner=’SCOTT’;

create public synonym DEPT for SCOTT.DEPT;
create public synonym EMP for SCOTT.EMP;
create public synonym BONUS for SCOTT.BONUS;
create public synonym SALGRADE for SCOTT.SALGRADE;

SQL>

4. This DDL can now be used to rebuild the public synonyms of a give schema or move the synonyms to another database.

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, SQL

Recreation of Oracle tablespace

Leave a comment

Oracle tablespaces provide storage of permanent and temporary data within the an Oracle database. The two forms of Oracle tablespaces are permanent which is used to store data which can survive restart of the database and temporary which is used for sort and other temporary storage requirements and does not require survival on restart of the database. In this article we will detail a simple script to recreation of the DDL necessary to transport a tablespace structure between databases. Feel free to modify the script to allow for more complex tablespace definitions.

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 22 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 a permanent tablespace to demonstrate the recreation of your DDL with the following statements.

create tablespace test_data datafile ‘/u02/ORADATA/ORCL10G/test_data01.dbf’ size 1m, ‘/u02/ORADATA/ORCL10G/TEST_data02.dbf’ size 1m;

3. Execute the following PL\SQL block to reproduce the DDL for a tablespace defined by the variable V_TS_NM.

set serveroutput on

spool ./create_tablespace.lst

declare

v_ct number:=0;
v_ct_files number;
v_ts_nm varchar2(30) := ‘TEST_DATA’;

begin

dbms_output.put_line(‘create tablespace ‘||v_ts_nm||’ datafile ‘);

select count(*) into v_ct_files from dba_data_files where tablespace_name=v_ts_nm;

for v_file in(select file_name, bytes/1024/1024 as v_size from dba_data_files
where tablespace_name=v_ts_nm)
loop

dbms_output.put_line(””||v_file.file_name||””||’ size ‘||v_file.v_size||’m’);

v_ct:=v_ct+1;

if v_ct

4. The statements produced can now be executed against another database to recreate this tablespace structure. NOTE: The directory structure on the destination server may be different from this system, so edit the physical structure of the datafiles if necessary.

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, 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