Category Archives: Oracle 10g

11g, Oracle, Oracle 10g, oracle 9i

Changing init parameters with an SPFILE:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to change initialization parameter from within an Oracle database. This procedure is the same on any OS.

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

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 27 09:49:04 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>

3. We use the alter system command to change initialization parameters from within the Oracle database while an SPFILE is in use. The following is an example of the alter system command.

alter system set {initialization_parameter} = {new_value} scope = {scope}

4. The key here is the scope clause which can have three possible values:

MEMORY – No changes are made to the SPFILE at all and the change is only mode in the current instance. To use this option, the initialization parameter must be dynamic and modifiable.

BOTH – Changes are made both in the current instance and in the SPFILE for future restart of the database. To use this option, the initialization parameter must be dynamic and modifiable.

SPFILE – Changes are not made in the current instance and the SPFILE is updated with new initialization parameter setting; however it will only take affect on restart of the database.

5. Examples of changing initialization parameter with alter system command:

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump’ scope=both;

System altered.

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump ‘ scope=memory;

System altered.

SQL> alter system set audit_file_dest=’/opt/oracle/udump ‘ scope=spfile;

System altered.

This completes adjusting initialization parameters in an SPFILE.

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

Oracle, Oracle 10g, oracle 9i

Determining if an initializing parameter is dynamic or not:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to determine if an initialization parameter is dynamic. This procedure is the same on any OS.

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

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 18 20:49:04 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>

3. Execute the following SQL statement: select NAME, ISSYS_MODIFIABLE from V$PARAMETER;

SQL> set pagesize 500
SQL> column name format a30
SQL> select NAME, ISSYS_MODIFIABLE from V$PARAMETER;
NAME ISSYS_MODIFIABLE
——————— ——————-
..
..
..
user_dump_dest IMMEDIATE
max_dump_file_size IMMEDIATE
audit_file_dest DEFERRED
LINUX_sched_noage FALSE
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
session_max_open_files FALSE
..
..
..
SQL>

4. Note: In the example above we have the NAME of the initialization parameter and three values for ISSYS_MODIFIABLE column, we can use this to determine if the initialization parameter is modifiable or not.

IMMEDIATE – The parameter is dynamic and can take affect immediately no matter if you are using a SPFILE or INIT file. When using an SPFILE you can use the clause SCOPE in the following ways: BOTH = change will take affect immediately and on restart of database; MEMORY = change will only take affect in current instance; and SPFILE = change will only take affect on restart of database. When using an INIT file you can use the clause SCOPE in the following ways: MEMEORY = change will only take affect in current instance.

DEFERRED – The parameter is not dynamic and will not take affect immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

FALSE – The parameter is not dynamic and cannot be changed immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

This completes determining if an initialization parameter is dynamic.

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

Oracle 10g, RAC, SQL

Taking an Oracle database out of a multi-Node RAC

Leave a comment

I have found during some administrative tasks it is easier to have your system in single NODE configuration. An example of this, would be physical changes the database structure that due not include binary upgrades such as a CPU patch. It is easier to startup, shutdown, and restrict access thorough single stand alone system, rather then a RAC environment with several active nodes. I realize that most of you are quite verse in the user of the SRVCTL utility to administrate the instances and database. However, I would like to show the use of starting up the database in single node from within the database and using SRVCTL just to verify the status.

1. Logon to one node as sysdba.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 18:47:59 2009

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

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

SQL>

2. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” and as you can see we are attached to a two node RAC.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1
2 node2 PRIMARY_INSTANCE mylinux2

SQL>

3. Exit out of SQL*PLUS and use the SRVCTL utility to obtain the current RAC status. The utility SRVCTL verifies what we saw in the data dictionary view of GV$INSTANCE.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is running on node mylinux2

4. Due to the fact that we have two instance running, we will use the SRVCTL utility to shutdown the database.

mylinux1>$ srvctl stop database -d orcl

5. Again use the SRVCTL utility to view the status of your Instances and the results are displayed that both instance are shutdown.

srvctl status database -d orcl
Instance node1 is not running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

6. Now you do have a choice here: 1. You could startup the database and a single instance with the SRVCTL utility or 2. You could logon to SQL*PLUS like a non-RAC system and startup the instance and database. We will logon to SQL*PLUS as sysdba and issue the startup command.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 19:02:13 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 1262332 bytes
Variable Size 587205892 bytes
Database Buffers 1191182336 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL>

7. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” to display the results of starting the database from NODE1.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1

SQL>

8. You can verify these results with the SRVCTL utility.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

9. Your system is now executing in a SINGLE NODE fashion and you can began your administrative tasks.

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

Oracle, Oracle 10g, RAC

Oracle RAC and difference between V$ and GV$ views

Leave a comment

Since the introduction of Oracle RAC, we have seen the additional system dictionary views of GV$ which reflect information across the various nodes of a RAC system. Unlike the V$ views of the data dictionary, which relate to the current status of the single node you are connected to, the GV$ allows you to see status throughout the system. In this article we will show how to view sessions through the entire system instead of just a single node.

1. Connect to NODE1 of your Oracle RAC system as a DBA users, in this example we connect as the user ljcatt..

mylinux>$ sqlplus ljcatt/ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:05:43 2009

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

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

SQL>

2. Connect to NODE2 of your Oracle RAC as sysdba.

mylinux>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:07:28 2009

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

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

SQL>

3. Use the SQL statement “select distinct(username) from v$session;” to view the current connections within NODE2 of your RAC. NOTE: You will not be able to see the connection from LJCATT in this dictionary view, because it only reflects connections to NODE2 and not the entire RAC.

SQL> select distinct(username) from v$session;

USERNAME
——————————

SYS

4. Wile still connected to NODE2 as sysdba, use the SQL statement “select distinct(username) from gv$session;” to view all sessions within the RAC. NOTE: Now you can see the connection to LJCATT.

SQL> select distinct(username) from gv$session;

USERNAME
——————————

LJCATT
SYS

SQL>

5. The GV$ dictionary views have the additional INST_ID column which give the node that the action is occurring in. Use the SQL statement “select inst_id from gv$session where username=’LJCATT’” to identify the NODE which LJCATT is currently connected to.

SQL> Select inst_id from gv$session where username=’LJCATT’;

INST_ID
———-
1

SQL>

The lesson learned, when you are administrating a RAC system use the dictionary views of GV$ and not V$.

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

11g, Oracle, Oracle 10g, oracle 9i, SQL

How Oracle handles the distinct clause between 10gR1 and 10g R2

Leave a comment

Oracle has changed the algorithm used to perform “select distinct” operations from 10gR1 and earlier version to 10gR2 and new versions. This has caught a lot of custom application and script writers by surprise, when they depended on the ordering of returned values from 10gR1 and earlier versions. The problem is caused by the way in which the Oracle parser handles a “select distinct” operation: From Oracle 8 to 10gR1, the parser used a sort algorithm to determine distinct values and from 10gR2 and later, the parser uses a hash algorithm to determine distinct values. The end result is that 8 to 10gR1 will return “select distinct” operations in sorted order and new version will not. So to resolve this issue you must use the “order by” clause to guarantee values are returned in sorted order. This article shows the plan differences between the two parsed statements in Oracle 10gR1 and 10gR2.

1. Logon to Oracle 10gR1 and execute “select version from v$instance to show the exact version of this release.

SQL> select version from v$instance;

VERSION
—————–
10.1.0.3.0

SQL>

2. Create the table test1 with the following DDL and insert ten numerical values.

create table test1(v_number number(10));

declare

v_ct number:=1;

begin

while v_ct<10
loop
insert into test1(v_number) values(v_ct);

v_ct := v_ct+1;

end loop;
end;
/

3. Select from this table with “select distinct” clause, as you can see the values are returned in sorted order even though we did not specify the order by clause.

select distinct v_number from test1;

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL>

4. Execute explain plan on the previous statement and you can see that the parser is performing a sort to find all distinct values in the table, thus returning the values in sorted order.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————

Plan hash value: 1260548514

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | SORT UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
——————————————————————–

Note

PLAN_TABLE_OUTPUT
———————————————————————
– dynamic sampling used for this statement

13 rows selected.

SQL>

5. Logon to 10gR2 server and select version from v$instance to show the exact RDBMS version.

SQL> select version from v$instance;

VERSION
—————–
10.2.0.4.0

SQL>

6. Create the table test1 and insert ten numerical values.

SQL> create table test1(v_number number(10));

Table created.

SQL>
SQL> declare
2
3 v_ct number:=1;
4
5 begin
6
7 while v_ct<10
8 loop
9 insert into test1(v_number) values(v_ct);
10
11 v_ct := v_ct+1;
12
13 end loop;
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>

7. Perform select distinct operation on test1 table and note that the values are returned in an unsorted order.

SQL> select distinct v_number from test1;

V_NUMBER
———-
1
6
2
4
5
8
3
7
9

9 rows selected.

SQL>

8. Perform a explain plain on your “select distinct” statement and note that the sort operation has been replaced by a hash operation, thus values will not be returned in a sorted format.

SQL> explain plan for select distinct v_number from test1;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————–

Plan hash value: 255531131

——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————
| 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 9 | 117 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 9 | 117 | 3 (0)| 00:00:01 |
———————————————————————-

Note

PLAN_TABLE_OUTPUT
——————————————————————-
– dynamic sampling used for this statement

13 rows selected.

SQL>

9. The reason for the change in the parser’s behavior is speed of execution; the hash algorithm is much more efficient then the sort operation and thus execution time decreases. In order to have a guaranteed sorting of your value, you must us an “order by” clause.

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

Oracle, Oracle 10g, oracle 9i, RMAN

ORACLE RMAN Full Database Backup

Leave a comment

Since the release of Oracle 8, Oracle has provided the Recovery Manger utility (RMAN) to perform database backup and recovery operations for Oracle databases. The utility has provided vast improvements over previous backup procedures. While there have been third party products to backup Oracle and other database systems, RMAN is a proprietary utility provided by Oracle and is freely available with your standard Oracle license. In this article we will review the procedures to perform a full database backup with the RMAN utility.

NOTE: This article assumes you have setup a recovery catalog. See article ‘Oracle 10g RMAN database Setup’ at http:/www.relidb.com/?p=80

1. Logon to your Oracle database server as the Oracle software owner.
2. Set your oracle SID to the database you wish to backup in this example the database is orcl10g.

mylinux: >set ORACLE_SID=orcl10g
mylinux: >

3. Execute the utility command RMAN.

Mylinux:> rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Aug 24 19:16:23 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN>

4. Connect to your target database and recovery catalog.

RMAN> connect target system/oracle
connected to target database: ORCL10G (DBID=879290681)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
RMAN>

5. Create your catalog with ‘CREATE CATALOG’ command.

RMAN> create catalog
recovery catalog created
RMAN>

6. Register the database with the ‘REGISTER DATABASE’ command.

RMAN> register database
2> ;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

7. Begin the backup of your Oracle database with the ‘BACKUP FULL’ command.

RMAN> backup full tag BACKUP_DB_ORCL format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.dbf’ d
atabase;

Starting backup at 24-AUG-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/U01/ORACLE/ORADATA/ORCL10G/SYSTEM01.
DBF
input datafile fno=00003 name=/U01/ORACLE/ORADATA/ORCL10G/SYSAUX01.
DBF
input datafile fno=00005 name=/U01/ORACLE/ORADATA/ORCL10G/EXAMPLE01
.DBF
input datafile fno=00002 name=/U01/ORACLE/ORADATA/ORCL10G/UNDOTBS01
.DBF
input datafile fno=00006 name=/U01/ORACLE/ORADATA/ORCL10G/USERDATA1
.DBF
input datafile fno=00004 name=/U01/ORACLE/ORADATA/ORCL10G/USERS01.D
BF
input datafile fno=00007 name=/U01/ORACLE/ORADATA/ORCL10G/TEST_DATA
01.DBF
input datafile fno=00008 name=/U01/ORACLE/ORADATA/ORCL10G/DATA02.DB
F
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765204_1_3_1_03KNH26K.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765270_1_4_1_04KNH28M.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-AUG-09

RMAN>

8. Begin the backup of your controlfiles with the ‘BACKUP CURRENT CONTROLFILE’ command.

RMAN> backup current controlfile format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.ctl’;

Starting backup at 24-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765322_1_5_1_05KNH2AA.CTL tag=TAG20090824T2008
42 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-AUG-09

RMAN>

9. Crosscheck the archivelogs, alter the archivelogs to current, and backup archivelogs

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN> sql ‘alter system archive log current’;

sql statement: alter system archive log current

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
validation succeeded for archived log
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
Crosschecked 1 objects

RMAN> backup format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_S%s_P%p_t%t_arch’ ( archivelog all delete inpu
t );

Starting backup at 24-AUG-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=1 stamp=695765354
input archive log thread=1 sequence=16 recid=2 stamp=695765359
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_S6_P1_T695765362_ARCH tag=TAG20090824T200921 comm
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_16_596BXGWB_.ARC recid=2 stamp=695765359
Finished backup at 24-AUG-09

RMAN>

10. That completes taking a complete database backup with the RMAN utility.

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