Looking to alter a database link but before going to modify it, i tried to get the DDL using:
DBMS_METADATA.GET_DDL('DB_LINK','<Link name>','<Owner>') but it gave me error that Link not found.
Tried with below query
SELECT 'CREATE '
|| DECODE (U.NAME, 'PUBLIC', 'PUBLIC ')
|| 'DATABASE LINK '''
|| DECODE (U.NAME, 'PUBLIC', NULL, U.NAME || '.')
|| L.NAME
|| ''' CONNECT TO '''
|| L.USERID
|| ''' IDENTIFIED BY VALUES '''
|| L.PASSWORDX
|| ''' USING '''
|| L.HOST
|| ''''
|| CHR (10)
|| ';'
TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
and it has given the output.
=================================================================
select user#, name, type# from user$ order by 1 --> Type# 0 for role/privilege 1 for user
=================================================================
While creating DB Link use "" to enclose passwords starting with Numbers or special characters.
=================================================================
OK! I’m calm and patient, and I understand. Now: about those database links!
Well, when you create a database link, you can technically call it anything you like. But Oracle strongly recommends that you follow the ‘global names’ convention, because Advanced Replication (and some other advanced features) won’t work unless you do. What that convention means is that your links are named in the format.. So, we just need to check what those parameters are set to (on the remote database) before anything else. :
SQL> connect system/dizwell@lx10
Connected.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- --------------
Are there any other parameters I need to check whilst I’m in this sort of mood?
One or two, yes. Most of them will be perfectly fine provided you always remember to name your databases properly (that is, with a name plus a domain name attached) when you first created them.
For a start, check what your remote database’s Global Database Name is. By default, that matches whatever DB_NAME + DB_DOMAIN imply, but it’s not dynamic... so if you’ve just changed your DB_DOMAIN, the Global Database Name won’t be what it needs to be. Still connected to the remote database, therefore, check:
SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'GLOBAL_DB%';
PROPERTY_NAME PROPERTY_VALUE
------------------ -------------------------------------------
GLOBAL_DB_NAME LX10.DIZWELL.LOCAL
And as you can see, my Global Database Name matches the name implied by DB_NAME + DB_DOMAIN perfectly (that’s because I always create my databases correctly!). If you do need to alter the Global Database Name, you’ll need to log on as SYS on the remote database and issue the command:
alter database rename global_name to;
Finally, you should really set one final (I promise!) parameter now needs to be set on the local database -that is, for me, on the WIN92 database running on Amethyst. In real life, it would be set to the same value on all instances throughout an organisation, because it makes no sense to have different databases running with different values. The parameter I’m talking about is called GLOBAL_NAMES, and it needs to be set to TRUE. There’s no absolute requirement for the parameter to be set in this way: but Advanced Replication won’t work without it. What it does is to force all database links to have names which match the global name of the database they’re connecting to. In other words, the link might request a connection to SALES, when technically the database’s Global Name is lx10.dizwell.local -and with GLOBAL_NAMES=TRUE, that connection will therefore fail. Only if they match exactly will the connection be allowed.
Again, that might seem a bit of an unnecessary restriction, but it makes managerial life much easier if that check is made. And, as I mentioned, advanced Oracle functionality demands it be set this way, so you might as well get used to it. Remember, though, the important point is that it is set on the local database, not the remote one:
SQL> connect system/dizwell
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
win92
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- --------------
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
As you can see, it’s a dynamic parameter, so setting it to its new value should not be a problem.
So, are we there yet? Can we actually create the database link now??!
Certainly! Creating the database link is actually the trivially easy part. The syntax is simply:
create database link
using ;
The real question is what goes in all those variable entries –the link name and so on?
Well, the link name bit is the fully-qualified global database name that was discussed earlier. In my case, that’s lx10.dizwell.local -and it has to be that because I’ve just set my GLOBAL_NAMES parameter to be TRUE. (I’d get an ORA-02085 error message now if I tried any other link name)
The schemaand password entries of the syntax are fairly self-explanatory: which bit of the LX10 database do I want to connect to? If you just want to select from Scott’s tables over the link, for example, then ‘Scott’ and ‘Tiger’ would be sensible entries (unless you’ve changed Scott’s password of course!). It is possible to create a database link that can select data from objects in many different schemas -but that’s a bit more involved, and the topic for another paper I might write one day on advanced database links! For now, I’m going to stick to single-schema links.
Finally, the tnsnames aliasentry is hopefully pretty obvious. It’s whatever is in the tnsnames.ora file on the local machine as the alias for the connection to lx10.dizwell.local. In my case, referring back to the series of screenshots I captured when first creating my tnsnames.ora, the friendly name I supplied there was LX10, so that’s what I’ll have to supply in my USING clause.
Putting all of that together, then, I can do this:
SQL> connect system/dizwell
What about DDL? Can I drop a table remotely, for example?
You know, at this point, knowing how to create these things for yourself, you are really supposed to go off and play with them and find these sorts of things out for yourself! But because I am a generous soul, just for once, I’ll show you:
SQL> drop table emp@lx10;
And if you think about it, it’s just as well commands like that one don’t work: I was syntactically asking to create a local index on a remote table... but since all the work of querying a remote table is performed on the remote database, what possible chance would there ever have been of that index being used?!
So the general rule is that queries and DML across a link are permitted, but DDL isn’t. When DML is performed, by the way, all referential integrity constraints are checked and applied as normal (though if it happens that the parent table, say, is in a different schema than the one referred to in the ‘create database link’ command you’ll have trouble).
These links are pretty neat, and they're working well. But it’s a pain to have to remember to keep adding the “@xxx” bit throughout my syntax. Isn’t there some easier way of referring to the remote database?
Sure. You can always create a view on the remote table, for example. That means you’d specify the remote database descriptor string once, and it would be stored thereafter ‘inside’ the view. All you’d have to do is query the view:
SQL> connect system/dizwell
It’s just occurred to me... How do I know whether someone is looking at my data remotely? How can I tell that someone is using a database link that connects to my database?
Excellent question. Everyone starts off enthusing about how great it is to pull data out of the remote database into your own... but clearly that’s a bit of a worry for the DBA trying to manage the remote database in the first place!
Well, the bad news is that you can’t tell if someone has merely created a link to your database.
SQL> connect system/dizwell@lx10
That’s not very fishy, though, is it? How would I spot Scott logged on amongst 3000 other users? What is there about him that suggests he’s actually the result of a database link?
Another excellent question. “With difficulty” is the obvious answer, but actually it’s do-able if you start looking at some of the other columns in the V$SESSION view. For example: :
SQL> select username, machine from v$session
What about subsequent administration of the links? Can I modify the schema the link uses, for example? And how do I know what links actually exist and where they connect to?
More bad news, I’m afraid: you can’t actually modify a database link once it’s been created. Syntactically, it’s not possible:
SQL> alter database link lx10.dizwell.local
Excellent. So is that about it for database links, then?
Sort of. You might be interested in just one further administrative detail: you can explicitly ask for a database link to be closed. A select statement that uses the link, for example, causes a server process to be created on the remote database, and that is a waste of CPU and memory on the remote server if you don’t intend to select from the link again for a long time. What’s more, the server process remains spinning away, consuming CPU on the remote database for as long as it takes you to end your session.
Prudence (and good application design rules) would therefore tend to suggest that there ought to be a way to manually close an infrequent-used database link so that the remote database is not swamped with the job of servicing links which aren’t actually doing anything productive. Thankfully, Oracle provided a command to do precisely that:
SQL> select * from emp@lx10;
DBMS_METADATA.GET_DDL('DB_LINK','<Link name>','<Owner>') but it gave me error that Link not found.
Tried with below query
SELECT 'CREATE '
|| DECODE (U.NAME, 'PUBLIC', 'PUBLIC ')
|| 'DATABASE LINK '''
|| DECODE (U.NAME, 'PUBLIC', NULL, U.NAME || '.')
|| L.NAME
|| ''' CONNECT TO '''
|| L.USERID
|| ''' IDENTIFIED BY VALUES '''
|| L.PASSWORDX
|| ''' USING '''
|| L.HOST
|| ''''
|| CHR (10)
|| ';'
TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
and it has given the output.
=================================================================
select user#, name, type# from user$ order by 1 --> Type# 0 for role/privilege 1 for user
=================================================================
While creating DB Link use "" to enclose passwords starting with Numbers or special characters.
=================================================================
What is a database link, when would I use one, and how do I go about creating one?
Database links are simply named objects that are created in one database and which describe how to connect to another, entirely different, database. Using a database link, it is possible, whilst connected to, say, the WIN92 database to issue the command select * from emp@lx10, and the data that is returned will be that from the EMP table in the LX10 database -an example, incidentally, which hints at the fact that a database link can be used to grab data out of a database that is a completely different version than the one you are directly connected to, and which could even be running a different operating system entirely (as my examples here will indeed show: 9i Release 2 running on Windows, and 10g running on Linux).
Links are thus a means of performing distributed database processing –the ability to distribute data around many different databases, on many different machines, in many different physical locations. Such an approach gives you redundancy (if the Melbourne machine blows up, it takes the Melbourne data with it, but Perth and Sydney continue working as before). It also gives you, potentially, performance benefits –with tables distributed amongst many machines, no one machine has to do all the hard work of resolving Users’ queries. (On the other hand, with data having to pass back and forth across the network, which is usually relatively slow, the performance benefits of distributed processing can easily be swamped by awful networking performance).
Database links are, in short, at the heart of Advanced Replication, which is Oracle’s complete infrastructure for distributed database processing. But they can also be used to perform small migrations and upgrades of databases, because of their platform and version independence. You wouldn’t upgrade a terabyte-sized 9i Windows database to a new life as a terabyte-sized 10g database on Linux this way, because again the network would probably not cope with that sort of traffic... but a few tens of gigabytes could be processed this way, probably.
Database links are simply named objects that are created in one database and which describe how to connect to another, entirely different, database. Using a database link, it is possible, whilst connected to, say, the WIN92 database to issue the command select * from emp@lx10, and the data that is returned will be that from the EMP table in the LX10 database -an example, incidentally, which hints at the fact that a database link can be used to grab data out of a database that is a completely different version than the one you are directly connected to, and which could even be running a different operating system entirely (as my examples here will indeed show: 9i Release 2 running on Windows, and 10g running on Linux).
Links are thus a means of performing distributed database processing –the ability to distribute data around many different databases, on many different machines, in many different physical locations. Such an approach gives you redundancy (if the Melbourne machine blows up, it takes the Melbourne data with it, but Perth and Sydney continue working as before). It also gives you, potentially, performance benefits –with tables distributed amongst many machines, no one machine has to do all the hard work of resolving Users’ queries. (On the other hand, with data having to pass back and forth across the network, which is usually relatively slow, the performance benefits of distributed processing can easily be swamped by awful networking performance).
Database links are, in short, at the heart of Advanced Replication, which is Oracle’s complete infrastructure for distributed database processing. But they can also be used to perform small migrations and upgrades of databases, because of their platform and version independence. You wouldn’t upgrade a terabyte-sized 9i Windows database to a new life as a terabyte-sized 10g database on Linux this way, because again the network would probably not cope with that sort of traffic... but a few tens of gigabytes could be processed this way, probably.
OK. Are they very hard to set up and get working?
Not really. They do rather depend on you having first configured all aspects of inter-machine and inter-database networking and communication, and to have done so successfully, though. If you are not sure how to configure a simple listener.ora or a tnsnames.ora, you’re going to find setting up a database link a bit tough. But assuming you know your way around those sorts of things, or are prepared to learn how by watching what I do below, then setting up a link itself is fairly painless.
Dare I ask you to show me how?!
Of course. For the purposes of what follows, you need to understand my network setup. I have a Windows machine running XP and called AMETHYST (IP address 192.168.1.39). I also have a Red Hat Enterprise Server 3 machine called OPAL (IP address 192.168.1.40). Before I go any further, therefore, I must check that I can ping one machine from the other, in either direction -and, of course, this being my network, configured correctly and with no fresh wombat bites through my network cables, two-way pinging is not a problem... so I can proceed. (The serious point there is that you would be amazed at the number of people who attempt to set up network links between database without first checking that their bare servers can do basic communicate with each other over the network in the first place. Oracle can't fix what's already not working!)
Amethyst runs a database called WIN92 -and as the name implies, it’s a 9i Release 2 database. Opal runs one called LX10, hopefully indicating that it’s a 10g database. To begin with, I want to be able to connect to Amethyst’s database directly, and select data from Opal via a database link.. I’ve already indicated that, to start with, I want Amethyst to be able to connect to a database on Opal. I will therefore need some form of names resolution mechanism on the Windows machine that allows me to resolve a database link name. For my simple home network, I’m going to use the local naming names resolution method -otherwise known as using a tnsnames.ora. You could do it equally well using Names Server or an LDAP-compliant directory service, but that's getting pretty advanced, and is outside the scope of this article to explain. If you've never created a tnsnames.ora before, then you can read (and see) how to do it by looking ai this set of screenshots and commentary. When you've worked your way through that, click to return back here to read on.
Now, that is only one half of the connection process. Amethyst now knows how to talk to Opal. But does Opal have a Listener running which can receive connection requests and know how to handle them? In my case, the answer to that question was ‘no’, so I needed to set up a new Listener on Opal. Once again, I've captured a set of screenshots and written a commentary on how you might go about creating a Listener here. Work your way through all of that, and then click to return back here to continue further.
Configuring a Listener in this way should mean that it is automatically running when you’re done. But you should check it -and, more importantly, check that it knows about instances on behalf of which it should be listening. Here’s how I checked mine:
[oracle@opal oracle]$ lsnrctl services
LSNRCTL for Linux: Version 10.1.0.2.0 - Production on 20-OCT-2004 07:49:39
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=opal.dizwell.local)(PORT =1521)))Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
Notice the command is lsnrctl services. If you’d selected a non-default name for your listener, it would have been lsnrctl services name_of_listener. The output I got indicates that my Listener is definitely running, but it’s not actually listening for my lx10 instance -that name appears nowhere. The reason for that is simply that my instance is not running!
If you ever need to start a Listener manually, by the way, the command is lsnrctl start (and lsnrctl stop to stop one!). Again, if you’ve got a non-default Listener name, then always include that name at the end of these commands.
[oracle@opal oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Oct 20 07:49:51 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startupORACLE instance started
Total System Global Area 138412032 bytes
Fixed Size 777796 bytes
Variable Size 112206268 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@opal oracle]$ lsnrctl services
LSNRCTL for Linux: Version 10.1.0.2.0 - Production on 20-OCT-2004 07:50:30
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=opal.dizwell.local)(PORT =1521)))Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "lx10.dizwell.local" has 1 instance(s).
Instance "lx10", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
So here you see me using boring old SQL*Plus to start my instance, and once it’s started, I issue the lsnrctl services command once more. And this time, I see mention made of something called ‘Service lx10.dizwell.local’ -which means my Listener will know how to deal with a request received to connect to a service of that name when the time comes.
I can test that now, by switching back to Amethyst, the Windows machine, and trying to connect to Opal’s database just as if I were an ordinary user:
C:\Documents and Settings\howardjr>sqlplus scott/newpwd@lx10
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 20 09:09:19 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
So, Scott can connect to the remote database, provided he remembers to ask for the connection to LX10 explicitly (otherwise, he ends up connecting to the local, Windows, database instead). Incidentally, before that test worked, I had to remember to play around with my Linux server’s firewall settings. Until I’d told it to trust connections coming via a particular network interface, it kept blocking connection requests, and I would always get an ORA-12541 TNS:No Listener error message. In these days of Service Pack 2 for XP -not to mention ZoneAlarm and so on- you might want to carefully check firewall options even on home PCs.
So that is basic network connectivity established... but we seem no nearer to setting up a database link than before...
Patience! The point here is: if a regular old user can’t connect to the remote database, how do you expect the database itself to do it, because that’s all a database link is: a database behaving as though it were a perfectly regular client. Getting service names, and firewalls, and global database names, and IP address resolution issues sorted out is getting us closer to setting up a database link, because they all need to be in place and sorted before we go any further.
Not really. They do rather depend on you having first configured all aspects of inter-machine and inter-database networking and communication, and to have done so successfully, though. If you are not sure how to configure a simple listener.ora or a tnsnames.ora, you’re going to find setting up a database link a bit tough. But assuming you know your way around those sorts of things, or are prepared to learn how by watching what I do below, then setting up a link itself is fairly painless.
Dare I ask you to show me how?!
Of course. For the purposes of what follows, you need to understand my network setup. I have a Windows machine running XP and called AMETHYST (IP address 192.168.1.39). I also have a Red Hat Enterprise Server 3 machine called OPAL (IP address 192.168.1.40). Before I go any further, therefore, I must check that I can ping one machine from the other, in either direction -and, of course, this being my network, configured correctly and with no fresh wombat bites through my network cables, two-way pinging is not a problem... so I can proceed. (The serious point there is that you would be amazed at the number of people who attempt to set up network links between database without first checking that their bare servers can do basic communicate with each other over the network in the first place. Oracle can't fix what's already not working!)
Amethyst runs a database called WIN92 -and as the name implies, it’s a 9i Release 2 database. Opal runs one called LX10, hopefully indicating that it’s a 10g database. To begin with, I want to be able to connect to Amethyst’s database directly, and select data from Opal via a database link.. I’ve already indicated that, to start with, I want Amethyst to be able to connect to a database on Opal. I will therefore need some form of names resolution mechanism on the Windows machine that allows me to resolve a database link name. For my simple home network, I’m going to use the local naming names resolution method -otherwise known as using a tnsnames.ora. You could do it equally well using Names Server or an LDAP-compliant directory service, but that's getting pretty advanced, and is outside the scope of this article to explain. If you've never created a tnsnames.ora before, then you can read (and see) how to do it by looking ai this set of screenshots and commentary. When you've worked your way through that, click to return back here to read on.
Now, that is only one half of the connection process. Amethyst now knows how to talk to Opal. But does Opal have a Listener running which can receive connection requests and know how to handle them? In my case, the answer to that question was ‘no’, so I needed to set up a new Listener on Opal. Once again, I've captured a set of screenshots and written a commentary on how you might go about creating a Listener here. Work your way through all of that, and then click to return back here to continue further.
Configuring a Listener in this way should mean that it is automatically running when you’re done. But you should check it -and, more importantly, check that it knows about instances on behalf of which it should be listening. Here’s how I checked mine:
[oracle@opal oracle]$ lsnrctl services
LSNRCTL for Linux: Version 10.1.0.2.0 - Production on 20-OCT-2004 07:49:39
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=opal.dizwell.local)(PORT =1521)))Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
Notice the command is lsnrctl services. If you’d selected a non-default name for your listener, it would have been lsnrctl services name_of_listener. The output I got indicates that my Listener is definitely running, but it’s not actually listening for my lx10 instance -that name appears nowhere. The reason for that is simply that my instance is not running!
If you ever need to start a Listener manually, by the way, the command is lsnrctl start (and lsnrctl stop to stop one!). Again, if you’ve got a non-default Listener name, then always include that name at the end of these commands.
[oracle@opal oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Oct 20 07:49:51 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startupORACLE instance started
Total System Global Area 138412032 bytes
Fixed Size 777796 bytes
Variable Size 112206268 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@opal oracle]$ lsnrctl services
LSNRCTL for Linux: Version 10.1.0.2.0 - Production on 20-OCT-2004 07:50:30
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=opal.dizwell.local)(PORT =1521)))Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "lx10.dizwell.local" has 1 instance(s).
Instance "lx10", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
So here you see me using boring old SQL*Plus to start my instance, and once it’s started, I issue the lsnrctl services command once more. And this time, I see mention made of something called ‘Service lx10.dizwell.local’ -which means my Listener will know how to deal with a request received to connect to a service of that name when the time comes.
I can test that now, by switching back to Amethyst, the Windows machine, and trying to connect to Opal’s database just as if I were an ordinary user:
C:\Documents and Settings\howardjr>sqlplus scott/newpwd@lx10
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 20 09:09:19 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
So, Scott can connect to the remote database, provided he remembers to ask for the connection to LX10 explicitly (otherwise, he ends up connecting to the local, Windows, database instead). Incidentally, before that test worked, I had to remember to play around with my Linux server’s firewall settings. Until I’d told it to trust connections coming via a particular network interface, it kept blocking connection requests, and I would always get an ORA-12541 TNS:No Listener error message. In these days of Service Pack 2 for XP -not to mention ZoneAlarm and so on- you might want to carefully check firewall options even on home PCs.
So that is basic network connectivity established... but we seem no nearer to setting up a database link than before...
Patience! The point here is: if a regular old user can’t connect to the remote database, how do you expect the database itself to do it, because that’s all a database link is: a database behaving as though it were a perfectly regular client. Getting service names, and firewalls, and global database names, and IP address resolution issues sorted out is getting us closer to setting up a database link, because they all need to be in place and sorted before we go any further.
OK! I’m calm and patient, and I understand. Now: about those database links!
Well, when you create a database link, you can technically call it anything you like. But Oracle strongly recommends that you follow the ‘global names’ convention, because Advanced Replication (and some other advanced features) won’t work unless you do. What that convention means is that your links are named in the format
SQL> connect system/dizwell@lx10
Connected.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- --------------
db_name string lx10
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- --------------
db_domain string dizwell.local
You can’t, of course, very easily change the DB_NAME parameter value, but you may need to change your DB_DOMAIN value. If you do, edit your init.ora or spfile in the usual way, but make sure you get your instance to re-notify the Listener of its new attributes. On 9i and above, that can be done by issuing the command alter database register, but in earlier versions, a restart of the instance was really called for.
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- --------------
db_domain string dizwell.local
You can’t, of course, very easily change the DB_NAME parameter value, but you may need to change your DB_DOMAIN value. If you do, edit your init.ora or spfile in the usual way, but make sure you get your instance to re-notify the Listener of its new attributes. On 9i and above, that can be done by issuing the command alter database register, but in earlier versions, a restart of the instance was really called for.
Are there any other parameters I need to check whilst I’m in this sort of mood?
One or two, yes. Most of them will be perfectly fine provided you always remember to name your databases properly (that is, with a name plus a domain name attached) when you first created them.
For a start, check what your remote database’s Global Database Name is. By default, that matches whatever DB_NAME + DB_DOMAIN imply, but it’s not dynamic... so if you’ve just changed your DB_DOMAIN, the Global Database Name won’t be what it needs to be. Still connected to the remote database, therefore, check:
SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'GLOBAL_DB%';
PROPERTY_NAME PROPERTY_VALUE
------------------ -------------------------------------------
GLOBAL_DB_NAME LX10.DIZWELL.LOCAL
And as you can see, my Global Database Name matches the name implied by DB_NAME + DB_DOMAIN perfectly (that’s because I always create my databases correctly!). If you do need to alter the Global Database Name, you’ll need to log on as SYS on the remote database and issue the command:
alter database rename global_name to
Finally, you should really set one final (I promise!) parameter now needs to be set on the local database -that is, for me, on the WIN92 database running on Amethyst. In real life, it would be set to the same value on all instances throughout an organisation, because it makes no sense to have different databases running with different values. The parameter I’m talking about is called GLOBAL_NAMES, and it needs to be set to TRUE. There’s no absolute requirement for the parameter to be set in this way: but Advanced Replication won’t work without it. What it does is to force all database links to have names which match the global name of the database they’re connecting to. In other words, the link might request a connection to SALES, when technically the database’s Global Name is lx10.dizwell.local -and with GLOBAL_NAMES=TRUE, that connection will therefore fail. Only if they match exactly will the connection be allowed.
Again, that might seem a bit of an unnecessary restriction, but it makes managerial life much easier if that check is made. And, as I mentioned, advanced Oracle functionality demands it be set this way, so you might as well get used to it. Remember, though, the important point is that it is set on the local database, not the remote one:
SQL> connect system/dizwell
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
win92
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- --------------
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
As you can see, it’s a dynamic parameter, so setting it to its new value should not be a problem.
So, are we there yet? Can we actually create the database link now??!
Certainly! Creating the database link is actually the trivially easy part. The syntax is simply:
create database link
connect to identified by
The real question is what goes in all those variable entries –the link name and so on?
Well, the link name bit is the fully-qualified global database name that was discussed earlier. In my case, that’s lx10.dizwell.local -and it has to be that because I’ve just set my GLOBAL_NAMES parameter to be TRUE. (I’d get an ORA-02085 error message now if I tried any other link name)
The schema
Finally, the tnsnames alias
Putting all of that together, then, I can do this:
SQL> connect system/dizwell
Connected.
SQL> create database link lx10.dizwell.local
2 connect to scott identified by newpwd
3 using 'lx10';
Database link created.
Notice how the tnsnames.ora alias goes inside single quotes, but nothing else does. Something else you need to be aware of: the link I’ve just created is private to SYSTEM, since it was SYSTEM that created it. If Scott tries to make use of it, for example, he gets this error:
SQL> connect scott/tiger
Notice how the tnsnames.ora alias goes inside single quotes, but nothing else does. Something else you need to be aware of: the link I’ve just created is private to SYSTEM, since it was SYSTEM that created it. If Scott tries to make use of it, for example, he gets this error:
SQL> connect scott/tiger
Connected.
SQL> select * from scott.emp@lx10;
select * from scott.emp@lx10
*ERROR at line 1:
ORA-02019: connection description for remote database not found
If you want lots of people to be able to use a link, you can issue this variant on the command instead:
SQL> connect system/dizwell
If you want lots of people to be able to use a link, you can issue this variant on the command instead:
SQL> connect system/dizwell
Connected.
SQL> drop database link lx10.dizwell.local;
Database link dropped.
SQL> create PUBLIC database link lx10.dizwell.local
2 connect to scott identified by newpwd
3 using 'lx10';
Database link created.
This time, the database link is created as a ‘public’ link, instead of private to the person who created it. Proof of that comes from Scott attempting to make use of it:
SQL> connect scott/tiger
This time, the database link is created as a ‘public’ link, instead of private to the person who created it. Proof of that comes from Scott attempting to make use of it:
SQL> connect scott/tiger
Connected.
SQL> select sum(sal) from emp@lx10;
SUM(SAL)
----------
1260
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
I’ve performed two selects from EMP here: one using the remote EMP table, and one the local version. The fact that the two tables return different total salary budgets indicate that data is definitely coming from two different sources, regardless of the fact that the table name in each case is identical.
I’ve performed two selects from EMP here: one using the remote EMP table, and one the local version. The fact that the two tables return different total salary budgets indicate that data is definitely coming from two different sources, regardless of the fact that the table name in each case is identical.
Cool! Can I do pretty much anything on the remote tables? What about performing DML, for example?
Well, remember that you are actually connecting to the remote database as the remote database’s Scott user -that connection information was, after all, hard-coded into the link’s definition. So if Scott can do select, inserts, updates and deletes on EMP, so can you when you use the database link that connects as him:
SQL> connect system/dizwell
Well, remember that you are actually connecting to the remote database as the remote database’s Scott user -that connection information was, after all, hard-coded into the link’s definition. So if Scott can do select, inserts, updates and deletes on EMP, so can you when you use the database link that connects as him:
SQL> connect system/dizwell
Connected.
SQL> select sum(sal) from emp@lx10;
SUM(SAL)
----------
1260
Notice here how, although I’ve connected to the WIN92 database as SYSTEM, I can select straight from a remote table called EMP -I don’t need to say ‘scott.emp’ because, as far as the remote database is concerned, I am Scott.
As for DML... well, as I said: if Scott can do it, so can you using a link that connects as Scott:
SQL> connect system/dizwell
Notice here how, although I’ve connected to the WIN92 database as SYSTEM, I can select straight from a remote table called EMP -I don’t need to say ‘scott.emp’ because, as far as the remote database is concerned, I am Scott.
As for DML... well, as I said: if Scott can do it, so can you using a link that connects as Scott:
SQL> connect system/dizwell
Connected.
SQL> update emp@lx10
set sal=sal*3;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select sum(sal) from emp@lx10;
SUM(SAL)
----------
3780
Just so you know, all the undo and redo generated by this transaction was generated on the remote database, not the local one. DML commands get processed exactly as if they had been issued with you connected directly, as a normal client, to the LX10 database.
Just so you know, all the undo and redo generated by this transaction was generated on the remote database, not the local one. DML commands get processed exactly as if they had been issued with you connected directly, as a normal client, to the LX10 database.
What about DDL? Can I drop a table remotely, for example?
You know, at this point, knowing how to create these things for yourself, you are really supposed to go off and play with them and find these sorts of things out for yourself! But because I am a generous soul, just for once, I’ll show you:
SQL> drop table emp@lx10;
drop table emp@lx10
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
SQL> truncate table emp@lx10;
truncate table emp@lx10
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
So, no DDL of any sort it permitted across the database link. If you want to DDL the EMP table, you’ll have to connect directly to the remote database to do it. As soon as you start to think about these things, though, all sorts of interesting permutations arise:
SQL> create index IDX_EMP on emp@lx10(ename);
So, no DDL of any sort it permitted across the database link. If you want to DDL the EMP table, you’ll have to connect directly to the remote database to do it. As soon as you start to think about these things, though, all sorts of interesting permutations arise:
SQL> create index IDX_EMP on emp@lx10(ename);
create index IDX_EMP on emp@lx10(ename)
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
And if you think about it, it’s just as well commands like that one don’t work: I was syntactically asking to create a local index on a remote table... but since all the work of querying a remote table is performed on the remote database, what possible chance would there ever have been of that index being used?!
So the general rule is that queries and DML across a link are permitted, but DDL isn’t. When DML is performed, by the way, all referential integrity constraints are checked and applied as normal (though if it happens that the parent table, say, is in a different schema than the one referred to in the ‘create database link’ command you’ll have trouble).
And can I join remote tables with local ones?
Of course. That's really the whole point of database links: they bring data to your local session, from any number of remote database, and make it look as if that data originated from the database you’re physically connected to. That would be a pretty pointless exercise if you couldn’t then treat that data exactly as if it were truly local -including joining it with any other tables you felt like or needed to:
SQL> select e.ename, e.sal, e.deptno, d.loc
Of course. That's really the whole point of database links: they bring data to your local session, from any number of remote database, and make it look as if that data originated from the database you’re physically connected to. That would be a pretty pointless exercise if you couldn’t then treat that data exactly as if it were truly local -including joining it with any other tables you felt like or needed to:
SQL> select e.ename, e.sal, e.deptno, d.loc
2 from emp@lx10 e, scott.dept d
3 where e.deptno=d.deptno;
ENAME SAL DEPTNO LOC
---------- ---------- ---------- -------------
MILLER 5400 10 NEW YORK
KING 5400 10 NEW YORK
CLARK 5400 10 NEW YORK
FORD 5400 20 DALLAS
ADAMS 5400 20 DALLAS
SCOTT 5400 20 DALLAS
Here, remote EMP is being joined with local DEPT. It’s just one of any number of joining possibilities.
Here, remote EMP is being joined with local DEPT. It’s just one of any number of joining possibilities.
These links are pretty neat, and they're working well. But it’s a pain to have to remember to keep adding the “@xxx” bit throughout my syntax. Isn’t there some easier way of referring to the remote database?
Sure. You can always create a view on the remote table, for example. That means you’d specify the remote database descriptor string once, and it would be stored thereafter ‘inside’ the view. All you’d have to do is query the view:
SQL> connect system/dizwell
Connected.
SQL> create view remote_emp as
2 select empno, ename, sal, deptno
3 from emp@lx10;
View created.
SQL> select * from remote_emp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 270 20
7499 ALLEN 270 30
7521 WARD 270 30
7566 JONES 270 20
7654 MARTIN 270 30
7698 BLAKE 270 30
7782 CLARK 270 10
7788 SCOTT 270 20
7839 KING 270 10
7844 TURNER 270 30
7876 ADAMS 270 20
7900 JAMES 270 30
7902 FORD 270 20
7934 MILLER 270 10
Notice that I can now query from the remote table syntactically as though it were local: no messy “@” symbols getting in the way this time. Synonyms could achieve the same sort of effect:
SQL> create public synonym rem_emp for emp@lx10;
Notice that I can now query from the remote table syntactically as though it were local: no messy “@” symbols getting in the way this time. Synonyms could achieve the same sort of effect:
SQL> create public synonym rem_emp for emp@lx10;
Synonym created.
SQL> select empno, ename, sal, deptno from rem_emp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 270 20
7499 ALLEN 270 30
7521 WARD 270 30
7566 JONES 270 20
7654 MARTIN 270 30
7698 BLAKE 270 30
7782 CLARK 270 10
7788 SCOTT 270 20
7839 KING 270 10
7844 TURNER 270 30
7876 ADAMS 270 20
7900 JAMES 270 30
7902 FORD 270 20
7934 MILLER 270 10
7499 ALLEN 270 30
7521 WARD 270 30
7566 JONES 270 20
7654 MARTIN 270 30
7698 BLAKE 270 30
7782 CLARK 270 10
7788 SCOTT 270 20
7839 KING 270 10
7844 TURNER 270 30
7876 ADAMS 270 20
7900 JAMES 270 30
7902 FORD 270 20
7934 MILLER 270 10
It’s just occurred to me... How do I know whether someone is looking at my data remotely? How can I tell that someone is using a database link that connects to my database?
Excellent question. Everyone starts off enthusing about how great it is to pull data out of the remote database into your own... but clearly that’s a bit of a worry for the DBA trying to manage the remote database in the first place!
Well, the bad news is that you can’t tell if someone has merely created a link to your database.
SQL> connect system/dizwell@lx10
Connected.
SQL> select username, sid, serial# from v$session
2 where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYSTEM 269 45
Here, I’ve made a direct connection to the remote database as SYSTEM, and there is no indication whatsoever that a database link has been created to LX10: the only user connected is the one I’ve literally just connected as: SYSTEM.
The slightly better news, however, is that if the database link is actually in use when you query V$SESSION on the remote database, you will be able to see something slightly fishy going on
SQL> connect system/dizwell@lx10
Here, I’ve made a direct connection to the remote database as SYSTEM, and there is no indication whatsoever that a database link has been created to LX10: the only user connected is the one I’ve literally just connected as: SYSTEM.
The slightly better news, however, is that if the database link is actually in use when you query V$SESSION on the remote database, you will be able to see something slightly fishy going on
SQL> connect system/dizwell@lx10
SQL> update emp@lx10 set sal=sal*10;
14 rows updated.
So this is me, connecting as SYSTEM in a new session, and using the link to LX10. What does a query of LX10’s V$SESSION now reveal?
SQL> select username, sid, serial# from v$session
So this is me, connecting as SYSTEM in a new session, and using the link to LX10. What does a query of LX10’s V$SESSION now reveal?
SQL> select username, sid, serial# from v$session
2 where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYSTEM 269 45
SCOTT 272 13
Well, you can certainly see a new session created: but it belongs to SCOTT, not SYSTEM, even though that’s who I logged on as in the other session. The reason, of course, is that I might be connected as SYSTEM on the local database, but the way I originally created the database link dictates that I connect to the remote database as Scott.
Well, you can certainly see a new session created: but it belongs to SCOTT, not SYSTEM, even though that’s who I logged on as in the other session. The reason, of course, is that I might be connected as SYSTEM on the local database, but the way I originally created the database link dictates that I connect to the remote database as Scott.
That’s not very fishy, though, is it? How would I spot Scott logged on amongst 3000 other users? What is there about him that suggests he’s actually the result of a database link?
Another excellent question. “With difficulty” is the obvious answer, but actually it’s do-able if you start looking at some of the other columns in the V$SESSION view. For example: :
SQL> select username, machine from v$session
2 where username is not null;
USERNAME MACHINE
------------------------------ ----------------------
SYSTEM DIZWELL.LOCAL\AMETHYST
SCOTT AMETHYST
Now, remember that I am connecting remotely to the database on OPAL from my Windows machine, AMETHYST, for both SYSTEM and the database link. Even so, the database link connection (that is, the one for ) is a bit different from ’s direct connection. The lack of a domain name in the column might indicate something a little piscatorial going on.
I will concede, however, that it is not hugely obvious, even so. When two Windows databases are linked, incidentally, you can see the server names as part of the OSUSER column in V$SESSION, which makes it extremely obvious. So clearly, the specific indicators to use may depend on your Oracle versions and platforms. The only advice I can really give, therefore, is to pay very close attention to views like V$SESSION: you might (or might not) spot something that gives you a clue.
Now, remember that I am connecting remotely to the database on OPAL from my Windows machine, AMETHYST, for both SYSTEM and the database link. Even so, the database link connection (that is, the one for ) is a bit different from ’s direct connection. The lack of a domain name in the column might indicate something a little piscatorial going on.
I will concede, however, that it is not hugely obvious, even so. When two Windows databases are linked, incidentally, you can see the server names as part of the OSUSER column in V$SESSION, which makes it extremely obvious. So clearly, the specific indicators to use may depend on your Oracle versions and platforms. The only advice I can really give, therefore, is to pay very close attention to views like V$SESSION: you might (or might not) spot something that gives you a clue.
What about subsequent administration of the links? Can I modify the schema the link uses, for example? And how do I know what links actually exist and where they connect to?
More bad news, I’m afraid: you can’t actually modify a database link once it’s been created. Syntactically, it’s not possible:
SQL> alter database link lx10.dizwell.local
2 connect to scott identified by tiger
3 using 'lx10';
alter database link lx10.dizwell.local
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter public database link lx10.dizwell.local
2 connect to scott identified by tiger
3 using 'lx10';
alter public database link lx10.dizwell.local
*
ERROR at line 1:
ORA-01901: ROLLBACK keyword expected
Try it any way you like, but there simply isn’t an ALTER command that will do the deed. Instead, any modification at all (such as changing the password the link uses to connect to the remote Scott schema, as in my examples here) requires that you completely drop the link, and re-create it from scratch:
SQL> drop database link lx10.dizwell.local;
Try it any way you like, but there simply isn’t an ALTER command that will do the deed. Instead, any modification at all (such as changing the password the link uses to connect to the remote Scott schema, as in my examples here) requires that you completely drop the link, and re-create it from scratch:
SQL> drop database link lx10.dizwell.local;
drop database link lx10.dizwell.local
*
ERROR at line 1:
ORA-02024: database link not found
SQL> drop public database link lx10.dizwell.local;
Database link dropped.
...and you will notice from my (deliberate!) error here that if you created the link as a public one, you have to specify the keyword PUBLIC when you drop it.
As for the data dictionary views needed to know what links exist and where they point to... Well, the main view is called DBA_DB_LINKS.
SQL> select * from dba_db_links;
...and you will notice from my (deliberate!) error here that if you created the link as a public one, you have to specify the keyword PUBLIC when you drop it.
As for the data dictionary views needed to know what links exist and where they point to... Well, the main view is called DBA_DB_LINKS.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------------- --------------------- ----------- --------- ------------
PUBLIC LX10.DIZWELL.LOCAL SCOTT lx10 20/OCT/04
There are equivalent USER_ and ALL_ views, of course -and hopefully, the information displayed in any of them is more or less self-explanatory. Note that the OWNER of the link is the person that created it, unless it was created as a public link, in which case a pseudo-user called PUBLIC is said to own it, as you can see in my example. Also: don’t get conned! The HOST column doesn’t tell you the “host” you’re connecting to at all (that is, the remote server name, which in my case is OPAL, remember). Rather it tells you what tnsnames alias is used to make the connection. You’d have to resolve that yourself by looking at your tnsnames.ora to work out what machine name was really meant.
Finally, be aware that there are three system privileges associated with database links: CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK and DROP PUBLIC DATABASE LINK. Note that there is no DROP DATABASE LINK privilege: it is assumed that if you’ve got the rights to create your own private links, you won’t be damaging anyone if that automatically implies the right to drop your own links. That’s obviously rather different from giving you the rights to drop someone else’s public link, which is why that gets a privilge of its own. Obviously, grant and revoke these privileges with care, just as you would any other system privilege.
There are equivalent USER_ and ALL_ views, of course -and hopefully, the information displayed in any of them is more or less self-explanatory. Note that the OWNER of the link is the person that created it, unless it was created as a public link, in which case a pseudo-user called PUBLIC is said to own it, as you can see in my example. Also: don’t get conned! The HOST column doesn’t tell you the “host” you’re connecting to at all (that is, the remote server name, which in my case is OPAL, remember). Rather it tells you what tnsnames alias is used to make the connection. You’d have to resolve that yourself by looking at your tnsnames.ora to work out what machine name was really meant.
Finally, be aware that there are three system privileges associated with database links: CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK and DROP PUBLIC DATABASE LINK. Note that there is no DROP DATABASE LINK privilege: it is assumed that if you’ve got the rights to create your own private links, you won’t be damaging anyone if that automatically implies the right to drop your own links. That’s obviously rather different from giving you the rights to drop someone else’s public link, which is why that gets a privilge of its own. Obviously, grant and revoke these privileges with care, just as you would any other system privilege.
Excellent. So is that about it for database links, then?
Sort of. You might be interested in just one further administrative detail: you can explicitly ask for a database link to be closed. A select statement that uses the link, for example, causes a server process to be created on the remote database, and that is a waste of CPU and memory on the remote server if you don’t intend to select from the link again for a long time. What’s more, the server process remains spinning away, consuming CPU on the remote database for as long as it takes you to end your session.
Prudence (and good application design rules) would therefore tend to suggest that there ought to be a way to manually close an infrequent-used database link so that the remote database is not swamped with the job of servicing links which aren’t actually doing anything productive. Thankfully, Oracle provided a command to do precisely that:
SQL> select * from emp@lx10;
EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17/DEC/80
7499 ALLEN SALESMAN 7698 20/FEB/81
[... snip ...]
7902 FORD ANALYST 7566 03/DEC/81
7934 MILLER CLERK 7782 23/JAN/82
14 rows selected.
SQL> alter session close database link lx10.dizwell.local;
ERROR
:ORA-02080: database link is in use
SQL> commit;
Commit complete.
SQL> alter session close database link lx10.dizwell.local;
Session altered.
Here, I first select from the remote EMP table. That causes my session to create a server process on the remote database. You can sort-of tell that something like that has happened, because when I then explicitly seek to close the link, it tells me it is in use -even though, obviously, the select statement that caused the link to become active has long since finished. Well, of course, the error message is trying to say, ‘Your report might not be active any more, but your session here still owns a live server process on LX10’.
But if I explicitly issue a commit (which comes as a bit of a shock considering I was only selecting, and not doing any DML, but that’s just the way you let the system know a select statement is definitely over), then the same explicit request to close the link works without fuss. The server process on the remote database is reclaimed, along with any memory it might have been consuming. It’s called being ‘tidy’! The exact same process (commit, then close the link) can be done after a DML statement uses the remote database, too.
Session altered.
Here, I first select from the remote EMP table. That causes my session to create a server process on the remote database. You can sort-of tell that something like that has happened, because when I then explicitly seek to close the link, it tells me it is in use -even though, obviously, the select statement that caused the link to become active has long since finished. Well, of course, the error message is trying to say, ‘Your report might not be active any more, but your session here still owns a live server process on LX10’.
But if I explicitly issue a commit (which comes as a bit of a shock considering I was only selecting, and not doing any DML, but that’s just the way you let the system know a select statement is definitely over), then the same explicit request to close the link works without fuss. The server process on the remote database is reclaimed, along with any memory it might have been consuming. It’s called being ‘tidy’! The exact same process (commit, then close the link) can be done after a DML statement uses the remote database, too.
No comments:
Post a Comment