I have a table defines as

create table CO.PRODUCTS
(
    PRODUCT_ID         NUMBER generated by default on null as identity
        constraint PRODUCTS_PK
            primary key,
    PRODUCT_NAME       VARCHAR2(255 char) not null,
    UNIT_PRICE         NUMBER(10, 2),
    PRODUCT_DETAILS    BLOB
        constraint PRODUCTS_JSON_C
            check (product_details is json),
    PRODUCT_IMAGE      BLOB,
    IMAGE_MIME_TYPE    VARCHAR2(512 char),
    IMAGE_FILENAME     VARCHAR2(512 char),
    IMAGE_CHARSET      VARCHAR2(512 char),
    IMAGE_LAST_UPDATED DATE
)
/

comment on table CO.PRODUCTS is 'Details of goods that customers can purchase'
/

comment on column CO.PRODUCTS.PRODUCT_ID is 'Auto-incrementing primary key'
/

comment on column CO.PRODUCTS.PRODUCT_NAME is 'What a product is called'
/

comment on column CO.PRODUCTS.UNIT_PRICE is 'The monetary value of one item of this product'
/

comment on column CO.PRODUCTS.PRODUCT_DETAILS is 'Further details of the product stored in JSON format'
/

comment on column CO.PRODUCTS.PRODUCT_IMAGE is 'A picture of the product'
/

comment on column CO.PRODUCTS.IMAGE_MIME_TYPE is 'The mime-type of the product image'
/

comment on column CO.PRODUCTS.IMAGE_FILENAME is 'The name of the file loaded in the image column'
/

comment on column CO.PRODUCTS.IMAGE_CHARSET is 'The character set used to encode the image'
/

comment on column CO.PRODUCTS.IMAGE_LAST_UPDATED is 'The date the image was last changed'
/

Now I want to export all non-null PRODUCT_DETAILS column values into files of .json. How can I do with sqlplus? Say...

$ pwd
/.../wherever

$ sqlplus / as sysdba

> 

> exit

$ ls -1
1.json
2.json
// 3.PRODUCT_DETAILS is null
4.json

Trying to export schema using the following command on several Oracle instances: two 19c and one 21c

$ expdp mySchema/myPwd@ORCL

- Adding CONTENT=ALL or SCHEMA=mySchema or SCHEMA=MYSCHEMA or DUMPFILE=dumpfile.dmp to _expdp_ command arguments does not help. - The user used in login is a DBA. - Tablespace is Users and temporary is Temp with Unlimited space and disk space has plenty of free space. - There is actual data in tables and there are hundreds of tables in the schema. All export jobs succeed without any errors, except that all dumps are 308kb but one 19c it actually dumps schema as dump file is ~100mb. Data Pump Wizard also creates dump 308 kb file. In case, DEFERRED_SEGMENT_CREATION parameter is True. The export log is:

Export: Release 19.0.0.0.0 - Production on Thu Feb 22 11:22:09 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "MYSCHEMA"."SYS_EXPORT_SCHEMA_01":  MYSCHEMA/********@oracle21c SCHEMAS=MYSCHEMA DUMPFILE=ded 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Master table "MYSCHEMA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MYSCHEMA.SYS_EXPORT_SCHEMA_01 is:
  ...\DPDUMP\DED.DMP
Job "MYSCHEMA "."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 22 11:22:51 2024 elapsed 0 00:00:39

What may be wrong with the schema as other schema export as expected creating hundreds of MB dump file but in this case dump has no content?

I migrated from an on-premise Oracle XE to Amazon RDS. It's running on a T3.medium server with gp3 storage (200 GB/12000 IOPS). Almost every fifth minute the alert.log says like this: "Thread 1 cannot allocate new log, sequence 10209 Checkpoint not complete" 3-4 seconds after that it logs another message: "Thread 1 advanced to log sequence 10209 (LGWR switch), current SCN: 472780758" And immediately after that: "ARC1 (PID:6427): Archived Log entry 10205 added for B-1189761742.T-1.S-10208 ID 0x7efecf3c1c4d LAD:1 [krse.c:4934]" Like this: Reading about the first message ("cannot allocate new log") it seems as a "bad" message. The typical advice is to increase the redo log file size. So I added 4 log files, each 512 Mb. The advice was to remove the old log (128 Mb each) files but they are almost always "ACTIVE" so I have just managed to remove 1. This is the current status: ... and I still get the error message in alert.log. The parameter archive_lag_target is set to 300 (5 minutes), so I guess that's why it switches log file every five minutes. (I don't think I can change that setting in RDS.) Looking at the disk operations in AWS doesn't imply that the disk is under too heavy load: Should i be worried about the message "Thread 1 cannot allocate new log..."? If yes: what can be the reason for it and what can I do about it? **Update:** When I issued a manual checkpoint using: EXEC rdsadmin.rdsadmin_util.checkpoint; ... it did a checkpoint in less than a second and all but one log file became INACTIVE. It seems as if it doesn't do automatic checkpoints.

I installed Oracle XE 21C on an old laptop running Oracle Linux 8.6. I followed the official documentation without getting any errors. I am trying to set the lsnrctl to the one located in /opt/oracle/product/21c/dbhomeXE/network/admin/listener.ora. I am attempting to connect from a different host to the database (Oracle Linux IP is 192.168.0.100). DEFAULT_SERVICE_LISTENER = XE SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = XE) (ORACLE_HOME = /opt/oracle/product/21c/dbhomeXE) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) However, lsnrctl stat shows this (already tried reloading it): LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 21-OCT-2022 17:53:30 Copyright (c) 1991, 2021, Oracle. All rights reserved. TNS-01106: Listener using listener name LISTENER has already been started [oracle@oserver ~]$ lsnrctl star LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 21-OCT-2022 17:53:32 Copyright (c) 1991, 2021, Oracle. All rights reserved. TNS-01106: Listener using listener name LISTENER has already been started [oracle@oserver ~]$ lsnrctl stat LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 21-OCT-2022 17:53:38 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date 21-OCT-2022 17:33:21 Uptime 0 days 0 hr. 20 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/oserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/homes/OraDBHome21cXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "eb920423f5e46b23e055000000000001" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "xepdb1" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully Which is not what I have set up in the listener.ora. I have confirmed the listener.ora file is in the correct location. How do I set lsnrctl to follow the listener.ora file? And, does that listener.ora configuration allow me to connect from a different host? Thanks in advance.

We have migrated a standalone Oracle database into an Oracle 21 multitenant database. We had run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO package procedure in the source database prior to migrating the database into a new PDB. When we had a massive query that uses parallel processing on the destination server run a long time, I tried to invoke DBMS_RESOURCE_MANAGER.CALIBRATE_IO in the PDB, and got the error ORA-65040: operation not allowed from within a pluggable database Which is verified in the doco. So I ran it in the CDB, worked fine. I queried the table DBA_RSRC_IO_CALIBRATE in the CDB, and got good results. When I connected to the PDB and queried that table, I got back the values that were set in the *source* database; not the values I set in the CDB. How do I get the data in the CDB to be applied in the PDB?

There are 2 pluggable db's in the same container db running on Oracle 21c SE2 21.0.0.0.0 One pdb runs ORA$_ATSK_AUTOSTS every 15 minutes and it runs for less than a minute, seen from dba_scheduler_job_run_details. On the other pdb it runs once a day for 30-50 minutes. I tried disabling it yesterday : exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); But it still ran again this morning. Logs in dba_scheduler_job_log imply that it was run manually, but I can't see how it was triggered ... dba_scheduler_job_log.ADDITIONAL_INFO = 'REASON="manual slave run"' My questions are as follows: - How can I find out what is triggering the job? - Why doesn't the disable proc work? - Why is it running outside of the maintnenance windows dba_autotask_schedule ? - Is there a way to configure the sample size so that it runs for a shorter time ?

I am trying to connect to my Oracle DB with Windows authentication, and it just doesn't seem to work - Whatever I do I keep getting "ORA-01017: invalid username/password; logon denied". I installed Oracle DB version 21c, and upon that created a new windows user as the Oracle Home User. My global DB name is orcl.ad11.siemens.net, and the PDB is orclpdb. I've created a new listener with the following configuration in **listener.ora**: LISTENER_2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY)))) SID_LIST_LISTENER_2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCLPDB.AD11.SIEMENS.NET) (ORACLE_HOME = C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home) (SID_NAME = ORCLPDB))) And added an alias in **tnsnames.ora**: LISTENER_2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = ORCLPDB.AD11.SIEMENS.NET))) When I execute "lsnrctl status LISTENER_2", I get: STATUS of the LISTENER ------------------------ Alias LISTENER_2 Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 05-DEC-2024 15:18:31 Uptime 0 days 0 hr. 1 min. 0 sec Trace Level admin Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home\network\admin\listener.ora Listener Log File C:\Oracle\OraDB21c\diag\tnslsnr\\listener_2\alert\log.xml Listener Trace File C:\Oracle\OraDB21c\diag\tnslsnr\\listener_2\trace\ora_23584_28284.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=.ad11.siemens.net)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\ORADB21C\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "75b0d2bd17c4485c998c967244b4515d.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "ORCLPDB.AD11.SIEMENS.NET" has 2 instance(s). Instance "ORCLPDB", status UNKNOWN, has 1 handler(s) for this service... Instance "orcl", status READY, has 2 handler(s) for this service... Service "bc74b627f47a416ab26784e660b5c7b2.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "orcl.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "orclXDB.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully I noticed the status of ORCLPDB is UNKNOWN but I have no idea why - guess it might be something to consider as the source of the problem. Additionally, I added the following line to **sqlnet.ora**: SQLNET.AUTHENTICATION_SERVICES = (NTS) And I also granted my current windows user with the required privileges, and it's verified by the fact that: ALTER SESSION SET CONTAINER = ORCLPDB; SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE AUTHENTICATION_TYPE = 'EXTERNAL'; Returns my current windows user. I use the following command to connect: sqlplus /@LISTENER_2 And tried many other ways as well, but keep getting that ORA-01017. Does anyone have a clue what is the problem here and how can I fix it?

I install Oracle 21c database and Oracle GoldaneGate 21.3 on Windows server, I can connect to my PDB using sqlplus and SqlDeveloper from another pc, but when try to dblogin from GGSCI get error this is my environment I set in my server: ORACLE_HOME = D:\oracle21c\WINDOWS.X64_213000_db_home LD_LIBRARY_PATH = D:\oracle21c\WINDOWS.X64_213000_db_home\lib TNS_ADMIN = D:\oracle21c\app\oracle\homes\OraDB21Home1\network\admin NLS_LANG = AMERICAN_AMERICA.AR8MSWIN1256 OGG_HOME = OGG_HOME PATH = ...;D:\oracle21c\WINDOWS.X64_213000_db_home\bin;D:\oracle21c\WINDOWS.X64_213000_db_home\lib get this error: GGSCI (Oracle-test) 2> dblogin userid ggs@mypdb Password: Error: Failed to initialize timezone information. Check location of ORACLE_HOME. I set ORA_TZFILE in environment ORA_TZFILE = D:\oracle21c\WINDOWS.X64_213000_db_home\oracore\zoneinfo\timezlrg_35.dat now I get this error: GGSCI (Oracle-test) 2> dblogin userid ggs@mypdb Password: Error: OCI Error ORA (status = 12715-Error while trying to retrieve text for error ORA-12715 ) I change mgr file in dirprm like this: PORT 7809 USERID ggs@mypdb, PASSWORD ggs and when run start manager in ggsci, have this error: 2024-11-25T02:29:57.852-0800 INFO OGG-03541 Oracle GoldenGate Manager for Oracle, MGR.prm: Oracle Environment Variables: TNS_ADMIN = D:\oracle21c\WINDOWS.X64_213000_db_home\network\admin PATH = C:\Program Files\Common Files\Oracle\Java\javapath;D:\oracle21c\WINDOWS.X64_213000_db_home\bin;D:\oracle21c\WINDOWS.X64_213000_db_home\lib;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;D:\oracle21c\ords\bin;D:\ggs;C:\Users\Administrator\AppData\Local\Microsoft\WindowsApps;. 2024-11-25T02:29:57.978-0800 ERROR OGG-00303 Oracle GoldenGate Manager for Oracle, MGR.prm: OCI Error ORA (status = ١٢٧١٥-Error while trying to retrieve text for error ORA-12715 ). 2024-11-25T02:29:57.994-0800 ERROR OGG-01668 Oracle GoldenGate Manager for Oracle, MGR.prm: PROCESS ABENDING. I don't config extract,pump or replicate!

I have a big table with more than one hundred million records, it has this schema. schema of the table: CREATE TABLE USER_B.BIG_TABLE ( field1 NUMBER (10), field2 NUMBER(19,0), field3 NUMBER(5,0), field4 NUMBER(25,0), field5 VARCHAR2(55 BYTE), field6 NUMBER(10,0), field7 VARCHAR2(250 BYTE), field8 NUMBER(5,0), field9 DATE, field10 NUMBER(5,0), field11 NUMBER(15,0), field12 NUMBER(10,0), field13 VARCHAR2(55 BYTE), field14 NUMBER(20,0), field15 NUMBER(10,0), field16 VARCHAR2(100 BYTE), field17 NUMBER(10,0), field18 NUMBER(5,0), field19 NUMBER(15,0), field20 NUMBER(15,0) ) PCTFREE 0 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "XXXX" PARTITION BY RANGE ("PARTITION_KEY") INTERVAL (NUMTODSINTERVAL(1,'DAY')) (PARTITION "SYS_P17408" VALUES LESS THAN (TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NO INMEMORY SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "XXXX" ); I want to get sample of the above table with the following query.When I run this query I receive data block corrupted error. insert into /*+parallel(2)*/ SAMPLE_table (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12, field13,field14,field15,field16,field17,field18,field19,field20) (select field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14,field15,field16,field17,field18,field19,field20 from user_b.big_table where field7 in (select field7 from user_b.big_table group by field7 having count(0)>50) or field13 in (select field13 from user_b.big_table group by field13 having count(0)>100)); commit; Error is here: Error report - ORA-01578: ORACLE data block corrupted (file # 17, block # 955795) ORA-01110: data file 17: '/u01/app/oradata/SIS_Name/dsltbs12.dbf' Also, I run select count(0) from big_table the same error accured. Would you please guide me how to solve the problem? Any help is really appreciated.

I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code:

static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE";
	   static final String USER = "AdministratorPDB1";
	   static final String PASS = "admin.admin";
	   static final String QUERY = "SELECT * FROM TestTable";

	   public static void main(String[] args) {

	      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
	         Statement stmt = conn.createStatement();
	         ResultSet rs = stmt.executeQuery(QUERY);
	      ) {		      
	         while(rs.next()){
	            //Display values
	            System.out.print(rs.getString("fam_name"));
                System.out.print("          ");
	            System.out.print(rs.getString("giv_name"));
                System.out.print("          ");
	            System.out.print(rs.getInt("age"));
                System.out.print("          ");
	            System.out.print(rs.getString("email") + "\n");
	         }
	      } catch (SQLException e) {
	         e.printStackTrace();
	      } 
	   }

For some reason, the program seems to print just the first row from the table. I can confirm the fact that there are more entries in that table if I open Windows Command Prompt, connect using sqlplus command, and run the SELECT * FROM TestTable;, query, all three rows are printed in the command line.

I also tried to loop over the rows like this: ``` ResultSetMetaData metaData = resultSet.getMetaData(); int numCols = metaData.getColumnCount(); while (resultSet.next()) { for (int i = 1; i Now, I know I might have not given enough information for a solve, but this is all I got. This is a hard problem to debug.

OracleDB version: 21c
Java version: jdk 1.8.0

Using this query:

SELECT
        A.CACHE_BUFFER_TOTAL,
        B.CACHE_BUFFER_USED,
        A.CACHE_BUFFER_TOTAL - B.CACHE_BUFFER_USED AS CACHE_BUFFER_FREE
    FROM
        (SELECT BYTES / 1024 / 1024 AS CACHE_BUFFER_TOTAL FROM V$SGAINFO WHERE NAME = 'Buffer Cache Size') A,
        (SELECT (SELECT COUNT(*) FROM V$BH) * (SELECT BLOCK_SIZE FROM V$BUFFER_POOL) / 1024 / 1024 AS CACHE_BUFFER_USED FROM DUAL) B;

... I can determine the total size of the buffer cache and the total used; however, I would like to know for each user how much the user's queries are occupying the buffer cache. Is there a simple way to achieve this in Oracle 21C?

1 I have installed Oracle Database 21c Enterprise Edition on Oracle windows 10 I am now trying to install Apex 22.2 . Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance administrator password. Executed the @apxchpwd.sql command, it asks me for user name, e-mail and password but then it gives an error as quoted string not properly ended, also Tried various other passwords, nothing seems to be acceptable. What can be the problem?

I installed Oracle Linux 21c on the remote machine, tested it in Linux shell, and it worked as expected (I can query the testing table I created). But I am stuck when trying to connect to the database from local SQL Developer client (Version 22.2.1.234). I downloaded 3 ODBC-related packages - instantclient-basic-windows.x64-21.7.0.0.0dbru.zip - instantclient-sdk-windows.x64-21.7.0.0.0dbru.zip - instantclient-odbc-windows.x64-21.7.0.0.0dbru.zip. Above were unzipped and put in C:\oracle\instantclient_21_7. I also set up the DSN following https://windowsreport.com/install-oracle-odbc-driver-windows-10/ . On my laptop (Windows 10), I found ORACLE_HOME location and then created the TNSNAMES.ORA file following https://www.databasestar.com/tnsnames-ora-file/ . I launched SQL Developer, but the Database Connection panel showed: *No TNS entries found in {USER Home dir=C:\Users\xxxx, TNS_ADMIN=C:\oracle\instantclient_21_7, ORACLE_HOME=C:\Users\xxxx\AppData\Local\Temp\Temp2_WINDOWS.X64_193000_db_home.zip\network\admin}*. I tried to ignore above warning and created an Oracle Connection (Connection Type = TNS; Network Alias = net_service_name), clicked Test button, and I got an error: *Status: Failed: IO Error: The Network Adaptor could not establish the connection…* I couldn’t figure out what went wrong. Can anyone help please? Thanks in advance.

In my Windows Oracle 21c XE installation I have the product installed under: C:\Oracle\product\21c Below that there is: dbhomeXE which contains the bin directory and also network. However, I also have: C:\Oracle\product\21c\homes\OraDB21Home1 This also has network/admin. It looks like dbhomeXE should be the Oracle home, but I have seen it also mentioned that it should be the directory above. I assume TNS_ADMIN should be $ORACLE_HOME/network/admin? But how does it matter anyway? What uses ORACLE_HOME? It isn't currently set on my system and sqlplus launches, although it can't find the PDB I have created. **EDIT:** The variables are used in a batch script to run sqlplus, so I am talking about the client side really, although both the server and the client are on the same PC.

We have a Java Spring boot application that writes 20 million plus rows in a table with 2 blob columns in a batched manner into an Oracle 21c DB. We are using JDBC batch insert for fast inserts. However, we have observed that regardless of the batch size (500 or 3000 etc) the insertion of each batch takes around 10 seconds. The AWR report of oracle suggests the following : 1. Blobs are written to this SYS_LOB0000040689C00004$$ segment which has a very high IO time 2. The same segment has the highest Global Cache Buffer Busy The table created uses all default values e.g. orderdoc blob DBA is suggesting : 1. Use CACHE instead of NOCACHE - I think this won't help as we are writing a lot to the DB. And CACHE will only help once we start reading. At the moment it's just a lot of write operations and no reads. Is my understanding correct? 2. DISABLE STORAGE IN ROW - I think this won't help as the blobs are not huge and are in KBs. Is my understanding correct? On the Java side, we are using ByteArryInputStream to create the blob and new StringReader(string) to store Clobs. Is there a more efficient way to write Blobs and Clobs on the java side?

Does current version of Oracle Database (21c) support nested transactions? I'm aware of Oracle DB supporting savepoints . However I'm looking for a way to make COMMINT not to write to disk for purpose of running tests.

I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines: WITH lines AS (SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 -10000))') tline FROM dual) SELECT ct.id,ct.x, ct.y, ct.z as clcorr, vt.z as clorig FROM lines, TABLE(sdo_util.getvertices(SDO_LRS.CONVERT_TO_LRS_GEOM(SDO_CS.make_2d(tline)))) ct JOIN table(sdo_util.getvertices(tline)) vt ON ct.id=vt.id; Source: Find polyline vertices where M-value is not cumulative length ------ That query works in dbfiddle for Oracle **18c**: - [Screenshot](https://i.sstatic.net/vPLGM.png) - [Fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6afc20f0c18d8f740cb711d8eb0cc9f1) But the same query produces an error in dbfiddle for Oracle **21c**: ORA-00904: "CT"."Z": invalid identifier - [Screenshot](https://i.sstatic.net/JSdyh.png) - [Fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=6afc20f0c18d8f740cb711d8eb0cc9f1) ------- Why doesn't the query work in dbfiddle for Oracle 21c?

In Oracle 21c, it looks like we can return a resultset as JSON text: SELECT json_object(*) from dual; result: {"DUMMY":"X"} That works in dbfiddle : **Question:** Is there a way to return the JSON text using pretty formatting? { "DUMMY":"X" } Related: Select JSON text of SDO_GEOMETRY using SQL