Daily Archives: August 22, 2009

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

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