UTL_FILE direcotry name changes oracle 19c

I’ve scripts where UTL_FILE.FOPEN is used and the parameter passing for directory is an absolute path i.e., /asr/file/path and the corresponding oracle directory name as ASR_ABC but after up-gradation to oracle 19c the parameter is expected to be direcotry name ASR_ABC instead of absolute path /asr/file/path.

If I pass an absolute path I get error as Invalid path.

Do I have to change all the files from absolute path to directory name? or is there any work around which can be done from database to avoid changes in all files?

Oracle 19c database instance doesn’t register with listener

I have just installed an oracle 19c (19.3.0.0.0 to be exact) database on a centOS 8 virtual machine. The databases instances can all be started with sqlplus on the host machine. I have set up listener.ora and tnsnames.ora based on an existing (functioning) oracle 12c, which has a very similar setup.

I did the initial installation using the .rpm from Oracle, thinking that would make things easier. It probably didn’t make things any more difficult, but it meant that the directory structure was not what I’d have expected. Still, it isn’t as though it is unusable, just located differently. Having done that, I did a basic install of the DB instances I wanted (using pretty much all default settings) with dbca.

Finally, I setup listener.ora and tnsnames.ora based on the working 12c installation. The problem is, no matter what I do, the DB instances don’t seem to be registering themselves with the listener.

The tnsnames.ora looks like this:

## initially copied from linuxoracle12 (20200617-1112)  LISTENER_TEMP =   (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))   TMF =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = tmf.my-company-name.com)     )   )  LISTENER_TMFDE =   (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))   LISTENER_TMF =   (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))   TEMP =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = TEMP.my-company-name.com)     )   )   TMFDE =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = TMFDE.my-company-name.com)     )   ) 

The listener.ora looks like this:

## initially copied from linuxoracle12 (20200617-1117)  SID_LIST_LISTENER =  (SID_LIST =   (SID_DESC =    (GLOBAL_DBNAME = TMF)    (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)    (SID_NAME = TMF)   )   (SID_DESC =    (GLOBAL_DBNAME = TEMP)    (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)    (SID_NAME = TEMP)   )   (SID_DESC =    (GLOBAL_DBNAME = TMFDE)    (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)    (SID_NAME = TMFDE)   )  ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))     )   ) 

As an example the initTEMP.ora looks like this:

## initially generated with dbca ## 20200617 added: *.local_listener='LISTENER_TEMP' TEMP.__data_transfer_cache_size=0 TEMP.__db_cache_size=855638016 TEMP.__inmemory_ext_roarea=0 TEMP.__inmemory_ext_rwarea=0 TEMP.__java_pool_size=0 TEMP.__large_pool_size=16777216 TEMP.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment TEMP.__shared_io_pool_size=0 TEMP.__shared_pool_size=301989888 TEMP.__streams_pool_size=0 TEMP.__unified_pga_pool_size=0 *.audit_file_dest='/opt/oracle/admin/TEMP/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/opt/oracle/oradata/temp/TEMP/controlfile/o1_mf_hgkpt5t0_.ctl','/opt/oracle/fast_recovery_area/TEMP/controlfile/o1_mf_hgkpt5w0_.ctl' *.db_block_size=8192 *.db_create_file_dest='/opt/oracle/oradata/temp/' *.db_domain='ubs-hainer.com' *.db_name='TEMP' *.db_recovery_file_dest='/opt/oracle/fast_recovery_area' *.db_recovery_file_dest_size=8256m *.diagnostic_dest='/opt/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=TEMPXDB)' *.local_listener='LISTENER_TEMP' *.open_cursors=300 *.pga_aggregate_target=378m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1134m *.undo_tablespace='UNDOTBS1' 

So far so good… I thought. But regardless of what I try, I cannot get the DB instances to register with the listener. When I run lsnrctl services, I get the following:

[oracle@linuxoracle19 admin]$lsnrctl services LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 12:11:01 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxoracle19.my-company-name.com)(PORT=1521))) Services Summary... Service "TEMP" has 1 instance(s). Instance "TEMP", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "TMF" has 1 instance(s). Instance "TMF", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "TMFDE" has 1 instance(s). Instance "TMFDE", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully  In various posts and what not, I have read that status UNKNOWN, whilst not seriously bad, actually means that the service in question is not registered with the listener. This is exemplified when I try to connect using NetBeans or SQLDeveloper. In both cases I get the error: IO Error: The Network Adapter could not establish the connection  Before anybody asks, yes, I did try tnsping. When I did, I got: [oracle@linuxoracle19 dbhome_1]$   tnsping TEMP 5  TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:02:19  Copyright (c) 1997, 2019, Oracle.  All rights reserved.  Used parameter files: /opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora   Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linuxoracle19.my-company-name.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEMP.my-company-name.com))) OK (10 msec) OK (0 msec) OK (0 msec) OK (0 msec) OK (10 msec) [oracle@linuxoracle19 dbhome_1]\$   trcroute TEMP  Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 16:05:39  Copyright (c) 1995, 2019, Oracle.  All rights reserved.  Route of TrcRoute: ------------------  Node: Client        Time and address of entry into node: ------------------------------------------------------------- 17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521  Node: Server        Time and address of entry into node: ------------------------------------------------------------- 17-JUN-2020 16:05:39 ADDRESS= PROTOCOL=TCP  HOST=linuxoracle19.my-company-name.com  PORT=1521 

Can anybody out there tell me what is going wrong? I really have no idea why the current setup doesn’t work.

Oracle PL-SQL “Regular expression” to replace each “(space)and(space)” with ‘,’ in a string

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I’m using the powerful regular_expression for this but it does not give me the desired result.

select regexp_replace('x=y and g=h and u=1','(^[[:space:]]*)AND(^[[:space:]]*)', ',') from dual; 

I was wondering if you could help me out here. thanks in advance.

Diagonalization in oracle separation between QMA and PP

Reposting from cstheorystackexchange for more visibility:

Diagonalization is a very common technique to find oracle separations. For example, it can be used to separate $$\cal{P}$$ and $$\cal{NP}$$, with the essential idea being that of constructing an oracle in stages and diagonalizing any $$\cal{P}$$ machine against that oracle. Similarly, diagonalization arguments can also be used to diagonalize a $$\cal{BQP}$$ machine against an oracle like the Grover oracle, and achieve a separation between $$\cal{BQP}$$ and $$\cal{QMA}$$. I was wondering whether I can use diagonalization (against $$\cal{QMA}$$ machines) to separate classes like $$\cal{QMA}$$ and $$\cal{PP}$$, or $$\cal{QMA}$$ and $$\cal{AWPP}$$. Is there any literature on these types of separations? A subtlety that I note is that for the diagonalization argument against $$\cal{BQP}$$ machines, the essential idea is that the quantum machine cannot “search for a needle in a haystack”, meaning that if there are an exponential number of query states to keep track of, a quantum machine is almost blind to the change in any one of them. However, if you have a prover as well as a quantum machine, the prover can just “give you the needle”; meaning, the prover can just send you the right state to query. Can diagonalization still work in these settings?

As one of the answers suggested, a way to approach an oracle separation between $$\cal{QMA}$$ and $$\cal{PP}$$ is to consider a language $$L$$, for any language $$A$$, such that $$L = \{1^{n} : |A \cap \{0, 1\}^{n}| > \frac{1}{2} 2^{n} \}.$$

Clearly, $$L \in \cal{PP}^{A}$$, and it is reasonable to think that $$L \notin \cal{QMA}^{A}$$ and we can use diagonalization to show it. However, for me, the analysis of the latter is getting complicated as I do not how to mathematically model the prover’s action: the prover can send any quantum state and can potentially send a quantum state unrelated to the previous one when the oracle is changed during diagonalization. I am looking for any reference or any explicit proof of this non-containment.

Are any two complexity classes equipped with an oracle to solve the halting problem equivalent?

Equip any complexity class $$C$$ and $$B$$ (to be more specific: any complexity class that contains only standard decidable problems) with an oracle $$O$$ to solve the halting problem. Is $$C^O = B^O$$ for any $$B$$ and $$C$$ that only contain problems decidable by a normal TM (meaning a TM with no access to an oracle (only the empty oracle))?

Postgresql MVIEW refresh from Oracle Materialized View Log

Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?

If that’s not possible, is a dblink from Postgresql to Oracle possible?

Oracle Sql: Join Multiple Tables with 1 Association Table

Above is the association between the different tables. Dotted lines are not on primary keys, but are inferred based on data.

This is the query I have so far:

SELECT distinct t3.assoc_id, t4.assoc_id FROM table_1 t1 LEFT OUTER JOIN assoc_table at ON at.assoc_id = t1.assoc_id LEFT OUTER JOIN table_2 t2 ON t2.detail_id = t1.detail_id LEFT OUTER JOIN  table_3 t3 ON t3.assoc_id = at.assoc_id AND at.table_id = 'table_3'AND t3.value_1 = t1.value_1 LEFT OUTER JOIN table_4 t4 ON t4.assoc_id = at.assoc_id AND t2.value_2 = t4.value_2 AND t3.value_1 = t4.value_1 WHERE t2.unique_id = 'X'; 

Note: I forgot to add Unique_ID in Table_1

extract only single value from multiple rows based on certain condition for same id in Oracle

I’m oracle beginner, and I’m having trouble shooting the below issue –

My table is –

+——-+——+——-+—— + | ID | GRP | ITDESC| DEN |
+——-+——+——-+—— + | 12345 | MANX | Apple | SendV | +——-+——+——-+—— + | 12345 | MANX | Apple | Manual| +——-+——+——-+——- | 12345 | MANX | Apple | UnVeri| +——-+——+——-+—— + | 12346 | MANX | Mango | UnVeri| +——-+——+——-+—— + | 12347 | MANX | PineAp| SendV| +——-+——+——-+—— + 12348 | MANX | Pine | Manual|

I am expecting –

+——-+——+——-+—— + | ID | GRP | ITDESC| DEN |
+——-+——+——-+—— + | 12345 | MANX | Apple | SendV | +——-+——+——-+—— + | 12346 | MANX | Mango | UnVeri| +——-+——+——-+—— + | 12347 | MANX | PineAp| SendV| +——-+——+——-+—— + | 12348 | MANX | Pine | Manual| +——-+——+——-+—— +

I have multiple rows that has only the DEN column different for the same id. My aim is – for the same id perform the check – If the Value of DEN is ‘Manual’ then check to see if there is ‘SendV’ in DEN column for that id. If it is present then consider ‘SendV’ otherwise consider ‘Manual’. Note in the provided example, the order is a random, the SendV can be in 2nd row or 3rd row or 4th row based on the action, however the text of DEN will be same as said above.

select * from table t1 where DEN IN (‘Manual’, ‘SendV’) .I am not aware how to write the condition??

Any suggestions/oracle query help is welcome.

Oracle query’s required

The variables $$a,b,c \in \{0,1\}$$, thus $$a^k, b^k, c^k \in \{0,1\}$$

I want to pass a query to an oracle that returns the coefficients of each term $$(1,a,b,c,ab,ac,bc,abc)$$ in the expansion of products such as this one $$(1-a+ab)(1-b+bc)(b-bc)$$. There could be more variables and more brackets to expand.

Do I require a single FP query to do this or something more?

can we create edition in postgre sql like in oracle create edition edition name?

create edition e1; it giving missing keyword error. n oracle we create like this is. i wanted to create to edition in PostgreSQL like splitting the post db into 2 parts to make it zero down time.