I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there.
If you ...
First of all, if you'd be using the data type text or varchar (without length modifier) instead of the mostly pointless varchar(n), you wouldn't be in this tight spot to begin with. If you really need to enforce a maximum length, use CHECK constraints for that, those can be altered without messing with the table structure.
More details in this related answer ...
I think the fundamental question you need to ask yourself is how much of a guarantee you need that this has propagated to all db's. Do you need every change to commit or roll back together? If so you should probably write a script in a programming language of your choice (Perl, Python, or the like), send the DO block there, and then use PREPARE TRANSACTION ...
Basically, dblink uses a libpq-style connection info string.
Add sslmode=require to your connection string or a more restrictive setting to disallow unencrypted transfers. More details in the manual here.
I would also advice not to have the password in your SQL statement at all. SQL statements may get logged. Or backups are stored in an insecure location ......
As you have nothing dynamically built in your query, you don't have to use EXECUTE at all.
Inside the function body, you can simply do an INSERT if temp_table already exists:
INSERT INTO temp_table (fielda, fieldb, fielbc)
SELECT a, b, c
FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
E'SELECT fielda, fieldb, ...
postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular ...
Depending on the DB2 platform, it may indeed be possible to call an Oracle (or other database) stored procedure from a DB2 stored procedure. With DB2 for Linux, Unix, and Windows the process of setting up communication with a foreign data source is called federation. You can read more about this in the manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/...
Here is what I think is happening. I don't think it is a bug.
You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)
It's not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.
Using the dblink extension, this is possible.
Just create a connection inside your function, supplying the database name:
SELECT dblink_connect('conn', format('hostaddr=127.0.0.1 port=5435 dbname=%I user=test password=test', 'foreign_test'));
SELECT dblink_exec('conn', 'CREATE TABLE t (id integer)');
Note that using the ...
What you described here is absolutely normal and intended. This feature is called a global database link:
It is a "side effect" when for example Oracle Internet Directory is in use, I have seen this at a few companies who have a lot of databases.
More and better ...
Community Wiki answer collecting answers left in question comments
The ODBC driver needs to be on the computer that connects to the Oracle server not on the Oracle server itself. Plus: you can't use dblink for this, Postgres' DBLink can only connect to other Postgres servers. But you can use a foreign data wrapper. You only need the ...
What about remote database side, what does it have to do?
There must have Oracle Net installed on both local and remote database servers.
And the user being used should have CREATE SESSION system privilege on the remote database.
On fixed user link, local database need to know ID and password set. Is the ID is a usual account with which someone can ...
Answer provided by John Eisbrener in a comment
The effective equivalent is a Database Link. Sadly there's not an identical equivalent to what OPENROWSET does in one Oracle command or statement. You have to declare the db link first before accessing it. If you don't want to edit a tnsnames.ora file, you can create the dblink using an in-line statement, ...
No. You'll need to create a new private database link. Public database links (and synonyms) are owned by a different user than private database links (and synonyms) and you can't alter an object to change the owner.
Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.
You cannot create Materialized View from Synonym. I don't know why would you like to that but we create materialized ...
That's exactly what foreign data wrappers and foreign tables were created for:
You create a foreign server pointing to the source server, and then a foreign table that enables access to that remote server.
Some examples for that are in the description of the Postgres foreign data wrapper:
The solution that worked for me was to use Advanced Security, with netmgr or directly edit the sqlnet.ora by adding the parameters:
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
Once this is done it is necessary to restart the listener, for ...
Creating a view in the Postgres database that assigns shorter names to the columns in question might be an option. It will not affect dependencies on the Postgres side while providing comfortably short column names to Oracle.
Your real problem is not about storing the password in a table securely, but the need for repeatedly changing and recreating the dblink.
Yes, you can store a password in a wallet, and no, you will not be able to retrieve the actual password from the wallet.
To store a password securely in a table, you can encrypt it (e.g. with DBMS_CRYPTO) and store the ...
I do not think that is possible.
Someone or something needs access to the Filesystem to read the tnsnames.ora.
When using the DB-link oracle will get the definition for RDBX from the tnsnames.ora file. This is transparent to the user, so the user will not notice. As @EdStevens suggested I would try to solve the political issues and work with the DBA.
You can configure a timeout for a query to a remote server. The documentation is here
( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 )
You can change this on a database with this script which sets a 100 second timeout
USE AdventureWorks2012 ;
The comment from @a_horse_with_no_name helped me solve the issue i.e., the extension was not installed in the right schema when attempting to restore the DB. When moved to the right schema the restore finished with no errors.
I now see that Balasz has provided a solution, too. My solution works if the name of the database is FooB, and I see now that that isn't the case here. So Balas will be right. But I leave this post because it seems not to be widely known that this works.
From Administrator's Guide, 31 Distributed Database Concepts,
Global Name as a Loopback Database ...
You can get all open dblink connections using dblink_get_connections().
Those that are not used can only be found by examining your function code like Erwin suggests. I thought that with SQL functions one can query for the dblink functions it depends on, but it happens not to be the case (actually, you can even drop the extension while having functions ...