generate | Catt Solutions

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

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

Catt Solutions

Tag Archives: generate

Create Oracle Public Synonyms

Recreate Oracle Sequence

Oracle tips and tricks.