Thread: pg_temp schema created while using DB Link
Hello Everyone,
We have an issue with DB_Link from Oracle to PostgreSQL. When we try to access tables from Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are automatically created in the database while using the query below.
create table as select * from table@oralink
Even if we reload the database, the temp schemas are still there.
Do you know the reason why the temp schemas are being created with DB_link?Does anyone has a solution for this problem?
Thank you
Regards,
Jill
We have an issue with DB_Link from Oracle to PostgreSQL. When we try to access tables from Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are automatically created in the database while using the query below.
create table as select * from table@oralink
Even if we reload the database, the temp schemas are still there.
Do you know the reason why the temp schemas are being created with DB_link?Does anyone has a solution for this problem?
Thank you
Regards,
Jill
Attachment
On Fri, 2020-05-08 at 10:11 +0400, Jill Jade wrote: > We have an issue with DB_Link from Oracle to PostgreSQL. When we try to access tables from > Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are automatically created > in the database while using the query below. > > create table as select * from table@oralink > > Even if we reload the database, the temp schemas are still there. > > Do you know the reason why the temp schemas are being created with DB_link? > Does anyone has a solution for this problem? These schemas contain temporary tables and are not connected to your connection from Oracle (unless you create temporary tables via that connection). They are an implementation detail and should not bother you. What is your problem with these schemmas? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hello
We do not have any temporary tables in the Oracle database. The temporary schema is created only while using the db_link. These temp schemas (pg_temp, pg_toast) are not disappearing even after restarting the database.
These are empty schemas that do not contain anything (tables, views etc). And these schemas keep on increasing each time the query using the db_link is run.
Why the db_link is causing these schemas? Is there any issue with db_link?
Thanks
Regards,
Jill
We do not have any temporary tables in the Oracle database. The temporary schema is created only while using the db_link. These temp schemas (pg_temp, pg_toast) are not disappearing even after restarting the database.
These are empty schemas that do not contain anything (tables, views etc). And these schemas keep on increasing each time the query using the db_link is run.
Why the db_link is causing these schemas? Is there any issue with db_link?
Thanks
Regards,
Jill
On Fri, 8 May 2020 at 10:36, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2020-05-08 at 10:11 +0400, Jill Jade wrote:
> We have an issue with DB_Link from Oracle to PostgreSQL. When we try to access tables from
> Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are automatically created
> in the database while using the query below.
>
> create table as select * from table@oralink
>
> Even if we reload the database, the temp schemas are still there.
>
> Do you know the reason why the temp schemas are being created with DB_link?
> Does anyone has a solution for this problem?
These schemas contain temporary tables and are not connected to your connection
from Oracle (unless you create temporary tables via that connection).
They are an implementation detail and should not bother you.
What is your problem with these schemmas?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Jill Jade schrieb am 08.05.2020 um 09:32: > We do not have any temporary tables in the Oracle database. The > temporary schema is created only while using the db_link. These temp > schemas (pg_temp, pg_toast) are not disappearing even after > restarting the database. The temp tables would be created in Postgres, not in Oracle. > Why the db_link is causing these schemas? Is there any issue with db_link? Which DB_Link are you referring to exactly? Postgres' "dblink" module can only connect to another Postgres server. So it must be something on the Oracle side.
On 5/8/20 12:32 AM, Jill Jade wrote: > Hello > > We do not have any temporary tables in the Oracle database. The > temporary schema is created only while using the db_link. These temp > schemas (pg_temp, pg_toast) are not disappearing even after restarting > the database. > > These are empty schemas that do not contain anything (tables, views > etc). And these schemas keep on increasing each time the query using the > db_link is run. Postgres will keep empty pg_temp schemas around rather then having to recreate them the next time they are needed. Not sure where the cutoff is for number? > > Why the db_link is causing these schemas? Is there any issue with db_link? > > Thanks > > Regards, > Jill > > > On Fri, 8 May 2020 at 10:36, Laurenz Albe <laurenz.albe@cybertec.at > <mailto:laurenz.albe@cybertec.at>> wrote: > > On Fri, 2020-05-08 at 10:11 +0400, Jill Jade wrote: > > We have an issue with DB_Link from Oracle to PostgreSQL. When we > try to access tables from > > Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are > automatically created > > in the database while using the query below. > > > > create table as select * from table@oralink > > > > Even if we reload the database, the temp schemas are still there. > > > > Do you know the reason why the temp schemas are being created > with DB_link? > > Does anyone has a solution for this problem? > > These schemas contain temporary tables and are not connected to your > connection > from Oracle (unless you create temporary tables via that connection). > > They are an implementation detail and should not bother you. > > What is your problem with these schemmas? > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > -- Adrian Klaver adrian.klaver@aklaver.com