Tom Kyte

How to activate the auto-index feature in Oracle 19c Enterprise Edition? To activate on PDB send me the error: <code>BEGIN dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); END; Informe de error - ORA-40216: funcion no soportada ORA-06512: en "SYS.DBMS_SYS_ERROR", linea 79 ORA-06512: en "SYS.DBMS_AUTO_INDEX_INTERNAL", linea 10967 ORA-06512: en "SYS.DBMS_AUTO_INDEX", linea 301 ORA-06512: en linea 1 40216. 00000 - "feature not supported" *Cause: The feature was not supported in the API. *Action: Modify the code to avoid usage of the feature.</code>

Hi, I have created an application that is tracking a set of processes that are being run from a master table. When a process is completed, a flag in a column name PROC_EXEC_STATUS changes from N to Y for each process. I want my oracle apex application to refresh when the flag for the each individual processes in the table is updated. I have used javascript but then because the processes do not have a defined running time, it just continues to refresh and sometimes is not in sync with the process running time. I am on apex version 24.2.

I have PL/SQL package with a function that returns a list of item numbers. This package/function is repeatedly called by online with multiple users; passing parameters to the function to use within the SQL statement(s). A new string (short list of item types) will now be passed to the function to narrow down the item numbers being returned. My question: is it better to use a Collection or a Global Temporary Table to insert the item types into, which will then be used in the where clause of the SQL statement to select the item numbers.

<u></u><u><b>We are building a system where:</b></u> - Application users connect using different DB users - They call DBMS_SCHEDULER.CREATE_JOB to create jobs - Jobs are always expected to execute in the application user(APPUSER) schema - We call external C++ functions via LIBRARY objects - We use VPD policies and also log changes based on SESSION_USER However, we observe that jobs run with SESSION_USER = job_creator, which is not always the same as application user. <b><u>Question:</u></b> <b>How can we ensure that DBMS_SCHEDULER jobs always run with SESSION_USER = application user, regardless of who initiates the creation?</b> <u><b>Reproducible test case:</b></u> Tested on Oracle 19.25 (Non-CDB installation) on RHEL 9.5. - APPUSER: Application schema and owner of all used objects - DBUSER: One of the application/database users (there are many such users with different DB names) Steps to reproduce: <u>1. Execute as SYS:</u> <code> create user appuser identified by ***; grant create session to appuser; grant create library to appuser; grant create procedure to appuser; GRANT CREATE ANY TABLE TO appuser; grant create sequence to appuser; alter user appuser quota unlimited on USERS; create user dbuser identified by ***; grant create session to dbuser; grant create job to dbuser; grant create any job to dbuser; </code> <u>2. Execute as appuser:</u> <code> CREATE OR REPLACE LIBRARY extproc_userinfo_lib AS '/app/bin/libgetuser.so'; drop function loguserinfo; CREATE OR REPLACE FUNCTION appuser.LogUserInfo RETURN BINARY_INTEGER AS LANGUAGE C LIBRARY extproc_userinfo_lib NAME "LogUserInfo" WITH CONTEXT PARAMETERS ( CONTEXT, RETURN int ); grant execute on appuser.LogUserInfo to public; drop table appuser.applog; create table appuser.applog ( id number generated always as identity primary key ,message varchar2(300)--, ,log_time timestamp default systimestamp ); select * from appuser.applog order by log_time desc; </code> <u>3. Login as OS user oracle to Linux(I use RHEL 9.5) and create a new file /app/bin/getuser.cpp with the following contents. It insert both SESSION_USER and CURRENT_USER to table APPLOG: </u> <code> #include <oci.h> #include <cstring> #include <cstdio> typedef struct OCIExtProcContext OCIExtProcContext; #define OCIEXTPROC_SUCCESS 0 extern "C" int LogUserInfo(OCIExtProcContext* ctx) { OCIEnv* envhp = nullptr; OCIError* errhp = nullptr; OCISvcCtx* svchp = nullptr; OCIStmt* stmthp = nullptr; const char* query = "insert into appuser.applog(message) " "SELECT 'SESSION_USER:' || SYS_CONTEXT('USERENV','SESSION_USER') || " "'; CURRENT_USER:' || SYS_CONTEXT('USERENV','CURRENT_USER') " "FROM dual"; if (OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp) != OCIEXTPROC_SUCCESS) return -1; if (OCIHandleAlloc(envhp, (void**)&stmthp, OCI_HTYPE_STMT, 0, NULL) != O...

We start getting alerts about a tablespace users that it was getting full, tablespace users contains 3 datafiles of 32GB each, but we found out that in one of the tables in tablespace users it has a Lob(67 GB), so we decided to moved that Lob to have it owns tablespace. Now we want to skink users tablespace and reduced to one datafile instead of 3. how we can do that? We are trying to moved all db objects in those datafiles, to a new tablespace, but the problem seems like when we try to move the above table that contains the lob column doing the alter table APEX_UNISOURCE.UOS_DOCUMENTS move ONLINE TABLESPACE USERS_NEW; but seems like is moving also the lob column to the new tablespace. How we can move that table excluding lob column?

I have a problem in defining shared folders in VMVirtualBox. As there ist no category for that it seams that this is the wrong place. So where can i send the problem that seems to be a bug?

With Always Free, there is no support. How do I get DBMS_CRYPTO grant? I tried using STANDARD_HASH, but it says STANDARD_HASH must be declared. So is there a way to get this access preferably to DBMS_CRYPTO? If not is there an alternative?

Refer to object-relational-developers-guide / Column Object Storage in Relational Tables If ?The null indicators for an object and its embedded object attributes occupy one bit each. Thus, an object with n embedded object attributes (including objects at all levels of nesting) has a storage overhead of CEIL(n/8) bytes.? And ?Since the null indicator is one byte in size, the overhead of null information for each row of the relational table is one for each object column of relational table.? Then where can I check that null indicator length is changed for an object column(in an relational table) with more than 8 attributes or where storage is changed ?because I didn?t see any changes in data length column for object column through all_tab_columns.

??? ?????? ?????? ??? ??? ?? ??????? APEX? ???? ?? ????? ???? ????? ??????? ????? ?????? ?? ?? ???????

Hi Team, From performance perspective, which is a better filter IN or NOT IN?

I've installed Oracle Secure Backup using osbws_install.jar on a couple of hosts and it works fine on all of them (i.e. I can list, add and delete credentials in the wallet without being prompted for password). But on one particular host, after installing, when I try to list the credentials using mkstore, it prompts for wallet password, even though there has been no password set for this. There isn't any parameter defined in sqlnet.ora for wallet directory, either. Any idea why it may be behaving this way? <code>$ mkstore -wrl <location of cwallet.sso> -listCredential Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: </code>

I need to report the database sizing to our management. Following are the questions: 1) Total space allocated and used for each Database instance (We have 14 each for 3 applications) 2) How is the growth pattern over the last few years. Year to Year change. My answer is 1) Run the following query in each instance SELECT 'ALLOCATED ', SUM(bytes) / 1024 / 1024 / 1024 AS "DB_SIZE-GB" FROM dba_data_files UNION SELECT 'USED ', SUM(bytes)/1024/1024/1024 from dba_segments; 2) Store this info in a user defined Table and insert a row with the date once a month, starting today. So I can provide some growth pattern in a year or couple of years from now. Can you help improve this in any way, since it is going to be sent to the Management.

I have been working on a project to password protect the files generated by Oracle forms and reports. Since this will only be for internal users, I have used PDFUSER, PDFOWNER utilities given by Oracle. The next tasks is to password protect the files generated by UTL_FILE. I tried to create job which zip the file and password protect it, however the idea was rejected citing performance issues because currently the files are being generated in txt and excel format. <code>BEGIN dbms_scheduler.create_job( job_name => 'TEST_PASS_ZIP', job_type => 'EXECUTABLE', job_action => '/bin/sh', number_of_arguments => 1, enabled => FALSE -- Test run only ); dbms_scheduler.set_job_argument_value( job_name => 'TEST_PASS_ZIP', argument_position => 1, argument_value => 'zip -P MySecret123 /win/Class2/SysGenRpt/FIN/testfile_password.zip /win/Class2/SysGenRpt/FIN/testfile_password.txt' ); dbms_scheduler.enable('TEST_PASS_ZIP'); END; /</code> I found that using https://github.com/antonscheffer/as_zip, we can zip the file. However, I am seeking answer where I can password protect the file at the time of file generation and user can simply open the file by providing the password. Not applying DBMS_CRYPTO as user cannot decrypt the file.

I need oracle 8i enterprise edition and Devloper 6i can u teel me the links for them to download I need them very much ...

Hi Tom, good morning My question is very clear and generic, and very important for me due to a current discussion in my team: We have a database in the followint situation, the worst scenario, I guess: - standalone (no RAC, no Dataguard) - Only one disk with all inside: data, flash_recovery_area (so archive logs), redo logs online and control files with not multiplexion (only one redo logs online member and only a control file copy) and the ORACLE_HOME - My dadabase is in archivelog mode, taking weekly backuppieces with RMAN, BUT, there is <b>NO RECOVERY CATALOG</b> (usded control file instead) and <b>AUTOBACKUP OF CONTROL FILE IS OFF</b> -default If there is a<b> MEDIA FAILURE, so the disk is destroyed,</b> although Veritas is making the backups, ?Is there a way to recover the database in this situation after a complete disk failure? ?could rman preveiw cuould help in any way? I think, it is not possible, but "someone" at my team says yes. ?am I wrong? Thanks a lot for your time and your help -Best regards

<code>-- Settings select banner from v$version; --Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free select * from v$nls_parameters where parameter in ('NLS_NUMERIC_CHARACTERS','NLS_TERRITORY','NLS_LANGUAGE'); -- NLS_LANGUAGE GERMAN 3 -- NLS_TERRITORY GERMANY 3 -- NLS_NUMERIC_CHARACTERS ,. 3 create table asktom_gpx_tab (i number ,xmldata xmltype ); INSERT INTO asktom_gpx_tab -- (i,xmldata) VALUES (1 ,xmltype( '<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <gpx> <trkpt lat="49.773210" lon="8.930600"><ele>340.8</ele></trkpt> <trkpt lat="49.772980" lon="8.930280"><ele>342.0</ele></trkpt> <trkpt lat="49.772740" lon="8.929900"><ele>341.5</ele></trkpt> </gpx>') ); commit; select elevation from asktom_gpx_tab t, xmltable('/gpx/trkpt' passing t.xmldata columns elevation varchar2(20) path 'ele') x1; -- 340,8 -- converted because NLS sets decimal sign to comma -- 342.0 -- why is this value not converted to comma??? -- 341,5 -- converted because NLS sets decimal sign to comma -- I found a Question here with a supposed solution but it doesnt work right select to_number(elevation,'FM9999D99','nls_numeric_characters = '',.''') from asktom_gpx_tab t, xmltable('/gpx/trkpt' passing t.xmldata columns elevation varchar2(20) path 'ele') x1; -- 340,8 -- ok -- 3420 -- This is definitely a wrong value -- 341,5 -- ok</code> Shouldnt all values be treated the same way?

What are the recommended BIG-IP F5 settings for routing HTTP traffic to ORDS 24.4 without SSL in an Oracle EBS-integrated environment?

Is there a commit size parameter for datapump import utility in the future? Currently there is none. I see direct path insert and parallel process are there and if so will adding commit size parameter help in faster data loading.

Hello and thank you for your response. Sorry I could not provide a live example SQL. I have an application running in JBoss connecting to a database Reporting_DB and executes SQLs there. The SQLs actually would access views in the Reporting_DB. The views are accessing tables which are in a remote DB, say ReadOnlyStandby_DB accessed over a DB link to a remote database. Here is a simple schematic: Application --Network--> Reporting_DB ---DB Link--> ReadOnlyStandby_DB ORM sql ---> View --> Table SQL Signature: Example of SQL seen in Reporting_DB: The application uses some ORM (hibernate), so the SQLs are always changing as seen in the Reporting_DB: select this_.Column1 as Column1_485_0_, this_.Column2 as Column2_485_0_, this_.Column3 as AUDIT3_485_0 etc. Example of SQL seen in ReadOnlyStandby_DB The sql seen in the ReadOnlyStandby_DB looks like this: SELECT "A1"."col1" ... FROM "TABLE_1" where CONDITION ... UNION ALL ... The users are complaining that the performance is slow, developers are sharing application logs that show SQLs are timing out with slowness. The final database where SQL is executed is a Data Guard read only Standby database. I have OEM on Reporting_DB and ReadOnlyStandby_DB but the app server (JBOSS) is external to my jurisdiction and have no access or insight. How can I get the following details: 1) Connect the dots for session: How to connect the dots from App, db session to Reporting_DB, db session to ReadOnlyStandby_DB 2) Session Trace: How to trace session coming from DB link on ReadOnlyStandby_DB 3) SQL Analysis: The SQL on Reporting_DB is not the same on ReadOnlyStandby_DB; it seems to change. How to connect SQL "a" on Reporting_DB and its related SQL "b" on ReadOnlyStandby_DB ?

Hello All I have created a Materialized view using the following code <code>CREATE MATERIALIZED VIEW "APPS"."XXBST_UNPACK_PENDING_MV" ("CUST_ACCOUNT_ID", "CUSTOMER_NAME", "SUPPLIER_ID", "SUPPLIER_NAME", "SHIPMENT_ID", "SHIPMENT_NUMBER", "UNPACK_DATE", "DAYS_IN_UNPACK_PENDING_STATUS") SEGMENT CREATION IMMEDIATE ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "APPS_TS_TX_DATA" BUILD DEFERRED USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT sysdate + (1/24/60)*30 USING DEFAULT LOCAL ROLLBACK SEGMENT USING TRUSTED CONSTRAINTS EVALUATE USING CURRENT EDITION DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS SELECT * FROM xxbst_unpack_pending_v;</code> This is in a non prod environment which has just been restored from a backup due to the code being changed in prod to this lowest level expanded code <code>CREATE MATERIALIZED VIEW "APPS"."XXBST_UNPACK_PENDING_MV" ("CUST_ACCOUNT_ID", "CUSTOMER_NAME", "SUPPLIER_ID", "SUPPLIER_NAME", "SHIPMENT_ID", "SHIPMENT_NUMBER", "UNPACK_DATE", "DAYS_IN_UNPACK_PENDING_STATUS") SEGMENT CREATION IMMEDIATE ON PREBUILT TABLE WITHOUT REDUCED PRECISION USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS EVALUATE USING CURRENT EDITION DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS SELECT "A1"."CUST_ACCOUNT_ID" "CUST_ACCOUNT_ID","A1"."CUSTOMER_NAME" "CUSTOMER_NAME","A1"."SUPPLIER_ID" "SUPPLIER_ID","A1"."SUPPLIER_NAME" "SUPPLIER_NAME","A1"."SHIPMENT_ID" "SHIPMENT_ID","A1"."SHIPMENT_NUMBER" "SHIPMENT_NUMBER","A1"."UNPACK_DATE" "UNPACK_DATE","A1"."DAYS_IN_UNPACK_PENDING_STATUS" "DAYS_IN_UNPACK_PENDING_STATUS" FROM (SELECT "A7"."CUST_ACCOUNT_ID" "CUST_ACCOUNT_ID","APPS"."XXBST_DEV_UTILS_PKG"."GET_CUST_SHORT_NAME"("A7"."CUST_ACCOUNT_ID") "CUSTOMER_NAME","A3"."VENDOR_ID" "SUPPLIER_ID","A3"."VENDOR_NAME" "SUPPLIER_NAME","A10"."SHIPMENT_ID" "SHIPMENT_ID","A10"."SHIPMENT_NUMBER" "SHIPMENT_NUMBER",MAX("A7"."CREATION_DATE") "UNPACK_DATE",TRUNC(SYSDATE)-TRUNC(MAX("A7"."CREATION_DATE")) "DAYS_IN_UNPACK_PENDING_STATUS" FROM "XXBST"."XXBST_TNT_SHPMNT" "A10","XXBST"."XXBST_TNT_MV_PLAN" "A9","XXBST"."XXBST_TNT_MV" "A8","XXICE"."XXICE_SM_RCV_HEADERS" "A7","XXBST"."XXBST_TNT_HEADERS_ALL" "A6","XXBST"."XXBST_TNT_LINES_ALL" "A5","XXBST"."XXBST_BOM_ORDER_HEADERS_ALL" "A4", (SELECT "A12"."VENDOR_ID" "VENDOR_ID","A12"."VENDOR_NAME" "VENDOR_NAME" FROM "AP"."AP_SUPPLIERS" "A12","AR"."HZ_PARTIES" "A11" WHERE "A12"."PARTY_ID"="A11"."PARTY_ID") "A3","XXBST"."XXBST_TNT_MV_PLAN" "A2" WHERE "A10"."SHIPMENT_ID"="A7"."SHIPMENT_ID" AND "A10"."SHIPMENT_ID"="A9"."SHIPMENT_ID" AND "A9"."PLAN_ID"="A8"."PLAN_ID" AND "A8"."MV_TYPE"='DELIVERY' AND "A8"."MV_STATUS"='UNPACK_PENDING' AND "A6"."SHIPM...