Tom Kyte

Hi Tom, This is a DDL question/observation, which puzzles me a bit: Given the following succesfull creation of a table in a schema PL, on Oracle 19c: <code>create table pl.constraint_test ( id integer, end_date date, constraint end_date_not_null check (end_date is not null) ); Table PL.CONSTRAINT_TEST created. </code> Now, consider the following two DDL statements: -- --DDL Statement 1: --I would expect this to fail, since the table has a constraint with that name already. <code>alter table pl.constraint_test add constraint END_DATE_NOT_NULL check (END_DATE is not null);</code> --DDL Statement 2: --I would expect this to fail, since this constraint (the semantics: that "END_DATE is not null") is on the table already. <code>alter table pl.constraint_test add constraint END_DATE_NOT_NULL2 check (END_DATE is not null);</code> The results are these: --DDL Statement 1: <code>alter table pl.constraint_test add constraint END_DATE_NOT_NULL check (END_DATE is not null); Error starting at line : 50 in command - alter table pl.constraint_test add constraint END_DATE_NOT_NULL check (END_DATE is not null) Error report - ORA-02264: name already used by an existing constraint 02264. 00000 - "name already used by an existing constraint" *Cause: The specified constraint name has to be unique. *Action: Specify a unique constraint name for the constraint. --DDL Statement 2: alter table pl.constraint_test add constraint END_DATE_NOT_NULL2 check (END_DATE is not null); Table PL.CONSTRAINT_TEST altered.</code> Consequences and Thoughts: The first constraint addition fails because Oracle does not allow two constraints with the same name to be created. This is completely as expected! The second constraint, in my expectation, really should fail, since it is a repetition of the requirement that "END_DATE is not null". But it does not: Now the table has not one, but two, check constraints requiring that "End_date is not null" For this table it will mean slower inserts, and updates, since the "same" constraint will here have to be checked not once, but twice instead. With indexing Oracle is very careful, not allowing the same set of columns, in the same order, and of the same index type, to be indexed twice. Apparently, a similar thing is not enforced for constraints. Are the results above expected, or do I have a point here? Cheers, K?re Kristoffersen Database Expert, Nordea Bank.

I need to union two database querieres for an export job. Any idea how this is formatted on a par file?

here is the test. <code>create table t1(id int, name char(10)); create table t2(id int, name char(12)); insert into t1 values(100, 'yang'); insert into t2 values(200, 'yang'); commit; -- return 1 row select * from t1, t2 where t1.name = t2.name and t1.name = 'yang'; -- return 1 row select * from t1, t2 where t1.name = t2.name and t1.name = rpad('yang', 10); -- return 1 row with tmp as ( select id, name from t2 where name = rpad('yang', 12) ) select * from t1, tmp where t1.name = tmp.name and t1.name = 'yang'; -- return 0 row with tmp as ( select id, name from t2 where name = rpad('yang', 12) ) select * from t1, tmp where t1.name = tmp.name and t1.name = rpad('yang', 10);</code> Why the last query return 0 row?

So I am able work out partition by reference with the DDL below, but can seem to figure out how to also get IOT too. The examples with IOT I have seen needed the PK defined in the create table DDL. Since my solution for making the PK on the child table with partition local required me building the PK as an alter. I cannot seem to figure out how to get both. <code> CREATE TABLE orders ( ORD_ID INTEGER GENERATED ALWAYS AS IDENTITY, order_date DATE, order_mode VARCHAR2(500) ) partition by range (ORD_ID) interval (100) (partition empty values less than (1) ) ; CREATE UNIQUE INDEX PK_ORDERS_PK ON ORDERS (ORD_ID) local ; ALTER TABLE ORDERS ADD CONSTRAINT "PK_ORDERS_PK" PRIMARY KEY (ORD_ID) ; CREATE TABLE ORDER_ITEMS ( order_items_ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, ORD_ID INTEGER NOT NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2), quantity NUMBER(8), CONSTRAINT FK_order_items_ORD_ID FOREIGN KEY(ORD_ID) REFERENCES orders(ORD_ID) ) PARTITION BY REFERENCE(FK_order_items_ORD_ID); CREATE UNIQUE INDEX PK_ORDER_ITEMS_PK ON ORDER_ITEMS (ORD_ID,order_items_ID) local ; ALTER TABLE ORDER_ITEMS ADD CONSTRAINT "PK_ORDER_ITEMS_PK" PRIMARY KEY (ORD_ID,order_items_ID) USING INDEX "PK_ORDER_ITEMS_PK" ENABLE;</code>

Hi, I have a column in table that have yet text in fields. Some of fields have in the end of text: distance: R1: a R2: b R3: c mm mm mm I want to insert before the first mm d/a, the second mm d/b and the third mm d/c. It will be : distance R1: a R2: b R3: c d/a mm d/b mm d/c mm Please can you help me to write the update table code as update table set column = ??. So example: in items we have R1: 43.5 R2: 44 R3: 45 mm mm mm. There are about 17000 row containing this informations. I want to introduce before mm a calculation: 337.5/43.5 = 7.76 mm for the first one. In final it will be R1: 43.5 R2: 44 R3: 45 7.76 mm 7.67 mm 7.5 mm. I think perhaps I must use regexp_replace in update table: update table set column = regexp_replace (column, ....); The regexp_replace is very hard to understand. That is my problem. Thanks.

I would like to create a table in an Oracle 19c DB (on premise) that is optimally designed for many parallel DML queries (INSERT/UPDATE). What do I have to pay attention to? Does partitioning help, with different tablespaces? How must sequences be created or should sequences be omitted? How should indices be created? ...

Hi Tom, I have a question regarding to add some constraint into a table. I have 2 tables defined as: <code> create table TableA ( fieldA varchar2(10), fieldB varchar2(10), fieldC varchar2(5), fieldD varchar2(10) not null, fieldE number(38) not null, Constraint TableAPK primary key (fieldE)); </code> <code> create table TableB( categorie varchar2(255), soort varchar(255) not null, code varchar(255) not null, status varchar(255), Constraint TableB primary key (code)); </code> In TableA; the values from fieldA, fieldB, fieldC and fieldD are used to populate the TableB on column code. The column soort in TableB is filled depending which kind of field are comming from TableA. As example: <code> TableA fieldA fieldB fieldC fieldD fieldE ?????- ?????- ?????- ?????- ?????- PAS304 344555 PG1AA 36415 305 TableB categorie soort code status ????????- ????- ?????- ?????? [null] ALT PAS304 free [null] MMS 344555 free [null] ROE PG1AA free [null] TOR 36415 free </code> My question: It is possible to create new constraints foreign-keys in TableA for fieldA, fieldB, fieldC and fieldD with the fields from TableB (soort, code). I tried something like this but I am not sure if this is what I want. <code> ALTER TABLE TableA ADD CONSTRAINT fieldA_FK FOREIGN KEY (fieldA) REFERENCES TableB (CODE); ALTER TABLE TableA ADD CONSTRAINT fieldB_FK FOREIGN KEY (fieldB) REFERENCES TableB (CODE); etc </code> Thanks in advance for your answer. With kind regards, Ricardo Barrera Ramirez

Hi Tom, I am trying to alter the tablespace to the transportation tablspace. First I do: alter tablespace users read only; Then do : exp sys/change_on_install file=02072001.dmp transport_tablespace=y tablespaces=users triggers=n constraints=n But I got the error as: Export: Release 8.1.5.0.0 - Production on Fri Feb 9 13:19:08 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production Export done in US7ASCII character set and US7ASCII NCHAR character set Note: table data (rows) will not be exported Note: constraints on tables will not be exported About to export transportable tablespace metadata... EXP-00008: ORACLE error 29341 encountered ORA-29341: The transportable set is not self-contained ORA-06512: at "SYS.DBMS_PLUGTS", line 1067 ORA-06512: at line 1 EXP-00000: Export terminated unsuccessfully I checked packages under sys account and see the package named 'DBMS_PLUGTS' is there. Could you point out how to solve the problem? Thanks. Pauline

Hi TOM, Sorry for asking this basic question (After two hours of searching, I am unable to find the answer) Let's say I have run opatch in an ora19 home to patch the binaries. But then I "forgot" to run datapatch. Is there any dictionary view to tell me that I "forgot"? BR Peter

I am working on a Business SaaS solution and my data is siloed using a tenant_id column. I am setting up REST APIs using ORDS (v24). The application will provide the ability to POST and GET the subscribers data. Each subscriber will be provided their own set of credentials for the REST API. In calls to the API, I am requiring the tenant_id to be passed in via the headers. It occurs to me that even though I am securing the API behind a set of credentials, there is nothing to prevent the subscriber from using their credentials to access the API, but spoof their tenant_id in the headers by simply guessing a different tenant_id. Is there a way in ORDS to identify the user accessing the API so that I can map that user to the tenant_id? If not, I already activate a fake application user primarily for the purposes of logging the transactions. So, I've considered securing that user with a password and requiring the password to also be passed in the headers. I appreciate your advice on the best approach to use.

Dear Ask Tom Team, can you suggest me the best way to have my "<b>datamarts</b>" tables, that summarized data(coming from the <b>oltp </b>database) using <b>analytical functions</b>,daily refreshed? Thanks Alessandra

how can i insert and read numeric value larger than 1000 digits into blob column in oracle

i have 1st table MIT_DOCUMENTS_LIST_MASTER with columns and data <code> ID DOCUMENTS_LIST 1 Passport Copy 2 Visa Application Form 3 Company Cover Letter 4 Invitation Letter </code> second table VISA_ON_ARRIVAL_COUNTRIES columns and data <code> id COUNTRY_ID COUNTRY_NAME VISA_REQUIRED VISA_ON_ARRIVAL_COUNTRIES DOCUMENTS_LIST CREATED_ON LAST_MODIFIED 1 45 Germany Y N 1:2:3:5:4:6:7:9 01/15/2026 01/15/2026 </code> third table only columns columns <code> CREATE TABLE VISA_DOCUMENTS_UPLOADS ( ID NUMBER GENERATED BY DEFAULT AS IDENTITY, COUNTRY_ID NUMBER, DOCUMENT_ID NUMBER, FILENAME VARCHAR2(4000 BYTE), FILE_MIMETYPE VARCHAR2(512 BYTE), FILE_BLOB BLOB, FILE_CHARSET VARCHAR2(512 BYTE), SAMPLE_DOCUMENT BLOB, FORM_TEMPLATE BLOB, UPLOADED_DOCUMENT BLOB, PREVIEW_DOCUMENT VARCHAR2(4000), FILE_COMMENTS VARCHAR2(4000 BYTE), CREATED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, CREATED_BY VARCHAR2(255 BYTE), UPDATED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, UPDATED_BY VARCHAR2(255 BYTE), CONSTRAINT pk_visa_documents_uploads PRIMARY KEY (ID) ); </code> now i have to create one report whn i select country that country documents list will be shown in the report like this <code> Docid Document Name SampleDocuments FormTemplates Upload Documents Preview Uploaded Documents Mimetype File Size 1 Passport Copy Download Download chose_file Preview 2 Visa Application Form Downloa Download chose_file Preview </code> you see the above report format where upload documemnts i need to upload document for each document and store in the database of table VISA_DOCUMENTS_UPLOADS and i have one button save when i click on that button then it should store in the database thisis the report query <code> SELECT d.ID AS DOCUMENT_ID, d.DOCUMENTS_LIST AS DOCUMENT_NAME, MAX(CASE WHEN vu.SAMPLE_DOCUMENT IS NOT NULL THEN '<a href="download_sample?p_id=' || vu.ID || '" target="blank">Download</a>' ELSE 'Sample not Available' END) AS SAMPLE_DOCUMENTS, MAX(CASE WHEN vu.FORM_TEMPLATE IS NOT NULL THEN '<a href="download_template?p_id=' || vu.ID || '" target="blank">Download</a>' ELSE 'Template not Available' END) AS FORM_TEMPLATES, '<input type="file" ' AS UPLOAD_FILLED_FORM_BUTTON, MAX(CASE WHEN vu.PREVIEW_DOCUMENT IS NOT NULL THEN '<a href="' || vu.PREVIEW_DOCUMENT || '" target="_blank" id="preview_document_' || vu.ID || '">Preview</a>' ELSE 'No Preview Available' END) AS PREVIEW_UPLOADED_DOCUMENTS, MAX(vu.FILE_MIMETYPE) AS Mimetype, ...

Hello, I accidentally dropped an index on a table (Oracle 19c) that was constructed on three columns; one of them was a foreign key. table: INDEX_DEFINITION: <code>CREATE INDEX "dba"."INDEX_SOP" ON "dba"."USR_SOP_STD" ("TS_ID", "TS_ASSET_SUBDOMAIN", "TS_TITLE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 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 "TT_DATA" ;</code> The column TS_ASSET_SUBDOMAIN is a foreign key. The impact on the performance was notable. I have a backup of the database before dropping the index. How can I check what exactly happened during the drop index? How can I fix this? Many thanks. Kind regards, Ioana Deaconu

Oracle provides an initialization method that will automatically run when the package is first accessed within a session. My question, is there a method that gets called when the session ends? The reason I am asking this is because I have a package that connects to LDAP. I have been having trouble because in batch jobs it is easy to overload on the number of connections. Something about the unbind does not execute and free the session right away. To that end I am making a change to allow for the session to have just one connection, but it is possible someone does not call the close routine (to unbind). So, I was wanting a way to insure that the close routine would automatically get called at the end of the session. <code> --example of the initialization method create or replace package body LDAP_Valmont is ldap_session DBMS_LDAP.SESSION; Session_Open BOOLEAN; procedure SetSessionVariables is begin Session_Open:= FALSE; end; begin SetSessionVariables; end; </code>

Hi Tom, I'm trying to get rid of a performance problem in APEX with a pipelined function within an db-package. Reading ora-documentations and searching the web/comunnities wasn't successful. Sorry if my question is already answered - i haven't found it. My situation: 1) for different facilities of our company there are (physical) separated oracle databases which hold production data. 2) each of the db-installations of 1) hold the same data-model. 3) there is a central APEX-info page for requesting data for each of the db from 1). In dependence of the user(groups), the APEX-info-page should extract the data from the proper database and put them into an dynamic report (about 5.000 to 10.000 rows). 4) To meet the requirements of 3) and to avoid multiple function programming I've programmed as followed at db-site (Oracle DB 12c Enterprise Ed. Rel. 12.1.0.2.0 - 64bit Production): 4.1) created db-links to each database of 1). 4.2) created user-defined type (create or replace xyz_t AS OBJECT) and therefore an table (create or replace type xyz_ct as table of xyz_t;) 4.3) created an package with an pipelined function (FUNCTION get_prod_Data(facility varchar2) RETURN xyz_ct PIPELINED) 5) programming the function 5.1) create an ref cursor 5.2) define dynamic statement to differ between the db-links. ('Select a, b, c from prod_tbl@' || dblink || ', prod_tbl2@' || dblink || where ...'). 5.3) open cursor for statement 5.2) loop fetch cursor into variables exit when cursor%NOTFOUND; pipe row (xyz_t(...)); end loop; close cursor; Confusion: c1) calling the function at the database "select * from table(package.get_prod_Data('niceCity1'));" will return the datasets within 1 second. c2) calling the function from APEX for an dynamic report, the datasets are returned > 2 minutes (!!!). c3) changing the implementation of the function to (without dynamic statement): for runvar1 in ( Select a, b, c from prod_tbl@dblink_c1, prod_tbl2@dblink_c1 where ...' ) loop pipe row (xyz_t(runvar1.a, runvar1.b, runvar1.c)) end loop; Effect: data is return at the databas and also at APEX within seconds (instead of minutes at APEX). c4) extracting data for the dynamic report directly on APEX with the origin select-statement ('Select a, b, c from prod_tbl@dblink_c1, prod_tbl2@dblink_c1 where ...') there is also an good performance. So it seems to be a subject of the cursor within the package/function called out of APEX. Why is it so fast calling the function at database-site but has rather poor performance at APEX-site? Any hint is appreciated. Cheers Peter

I completed the course Database for developers foundation. I did not recieve an email of the certofocate but i did have the option to print the certoificate. but I want to have a URL that can show as proof of creidentials. where to get it? Please help I invested lot of time doing this course these past 2 weeks. Thank you

Hello All, I have a PL/SQL function that takes two INTERVAL DAY TO SECOND parameters and returns a value of the same data type. It works correctly as a regular PL/SQL function. I try to convert it to an equivalent SQL MACRO, both as a TABLE (in 19c and 23ai ) and as a SCALAR macro (in 23ai ) and they compile without error, but when trying to use them from a SELECT statement they both fail. I cannot perform tracing for trying to debug the problem. Also, using DBMS_UTILITY.EXPAND_SQL_TEXT cannot be used in this case, because it works only for a TABLE SQL Macro and only when the SQL runs successfully. As an enhancement suggestion, it would be extremely useful to have the ability to see the transformed SQL query ( after the macro replacement ) in all cases, TABLE or SCALAR, successful or not, either by using DBMS_UTILITY.EXPAND_SQL_TEXT or by any other method. I created a script for the test case, under the link below: https://livesql.oracle.com/ords/livesql/s/czlvwpdwtohm5u5l6njsmw4h9 and, although it appears as "Publicly shareable", the submission page DOES NOT allow me to enter it in the URL box ! Thanks a lot in advance for your help ! Iudith Mentzel ( 37 years of using Oracle, not just 35 as the dropbox on this submit page allows :) )

Starting with 21c (and continued in 23ai) I've noticed that Oracle database does not "automatically sort" data when working with set operators such as UNION, MINUS, INTERSECT etc, while it used to sort data till 19c. Is this behavioral change documented somewhere? <b>Example Code:</b> <code> WITH sample_data_1(c_id, c_val) AS( SELECT 1002,'V2' FROM dual UNION SELECT 1001,'V1' FROM dual ), sample_data_2(c_id, c_val) AS( SELECT 1004,'V4' FROM dual UNION SELECT 1003,'V3' FROM dual ) SELECT * FROM sample_data_1 UNION SELECT * FROM sample_data_2 ; </code> <b>OUTPUT in 21c and 23ai:</b> <code>C_ID | C_VAL ---- + -- 1002 | V2 1001 | V1 1004 | V4 1003 | V3</code> <b>OUTPUT in 19c and below:</b> <code>C_ID | C_VAL ---- + -- 1001 | V1 1002 | V2 1003 | V3 1004 | V4</code>

Greetings, How does the data structure of a compound index in Oracle look like? I'm wondering how Oracle is able to use a "skip scan" by using the second entry in a compound index? I know that the first field uses a b-tree structure, but I am unsure how the second field is stored. I started to think about this recently since I have been supporting MongoDB. In MongoDB, it is not able to use a compound index if the prefix field isn't part of the query. I've added the following as optional information to show a little bit about how Mongo stores a compound index and to show why Mongo can't use the secondary field of a compound index because the value of the second field is just concatenated into the value of the first field. For instance, in Mongo, if we have the following data: "test" has these four docs/records: { x: 1, y: 2 } { x: 2, y: 3 } { x: 2, y: 1 } { x: 1, y: 4 } Next, we create a compound index: db.test.createIndex({x: 1, y: 1}) Then we search for records that have x=2 and y >= infinity and get explain plan: Mongo reports that only two index leaf keys were read. This means that Mongo stores entries in the b-tree leaf as: (field1:field2) Thanks for your help, John