DBA Blogs

Hi , I am new to PLSQL and i am trying to insert data into table using insert into partition (partition_name) . When i am trying to load the data its saying the 'specified partition is not exixisting' . Please help me in this. The query is mentioned below declare v_start_time timestamp; v_end_time timestamp; v_last_day timestamp; v_partition_date number ; v_newpart VARCHAR2(30); v_constant varchar2(5); v_IMPRESSION_DATE impression_temp_1.IMPRESSION_DATE%TYPE; begin v_start_time := to_timestamp('01-04-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss'); v_end_time := to_timestamp('01-04-2015 23:59:59', 'dd-mm-yyyy hh24:mi:ss'); v_last_day := to_timestamp('10-04-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss'); v_partition_date := 20150401; v_constant := 'P_'; v_newpart := concat(v_constant , v_partition_date); dbms_output.put_line(v_newpart); WHILE v_start_time <= v_last_day LOOP dbms_output.put_line(v_newpart); insert into IMPRESSION_TEMP_1 partition (v_newpart) ( IMPRESSION_DATE , IMPRESSION_TIMESTAMP , RESPONSE_TIMESTAMP , REP_CREATED_DATE_TIME , REP_CREATED_BY , REP_LAST_UPDATED_DATE_TIME , REP_LAST_UPDATED_BY ) select N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP , N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP , N_NBA_DATA_IMPRESSION.RESPONSE_TIMESTAMP , N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP as REP_CREATED_DATE_TIME , 'ARDP0B34' as REP_CREATED_BY, N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP , 'ARDP0B34' as REP_LAST_UPDATED_BY FROM DATA_IMPRESSIONDATA_IMPRESSION WHERE "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" >= v_start_time and "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" <= v_end_time; v_start_time := v_start_time + 1 ; v_end_time := v_end_time + 1 ; v_partition_date := v_partition_date + 1; end loop; commit; end; Error report: ORA-02149: Specified partition does not exist ORA-06512: at line 19 02149. 00000 - "Specified partition does not exist" *Cause: Partition not found for the object. *Action: Retry with correct partition name. P_20150401 P_20150401 The DBMS_OUTPUT of the query shows P_20150401 is the values of the variable v_newpart) But The partition with the name P_20150401 actually exist in the temp table . SELECT partition_name FROM user_tab_partitions WHERE table_name = 'IMPRESSION_TEMP_1' and partition_name = 'P_20150401' ; PARTITION_NAME ------------------------------ P_20150401 . So could you please advise me why insert into IMPRESSION_TEMP_1 partition (v_newpart) is not taking the value P_20150401 ?

Hi Tom ! I have 2 databases, A and B. Created a database link from A pointing to B. Let's call it "dblinkB". In database B there's a table "mytable" with a trigger, that captures information from the instance using V$SESSION. I make inserts from A in this table "mytable" like "INSERT INTO MYTABLE@<b>dblinkB</b> VALUES('myvalues');". I would like to track the database identification from which database this INSERT was made, for logging purposes. Tried to use SYS_CONTEXT variables, but all the information I receive is concerned to database B. Thank you ! Antonio

Hi Tom Can you please give an example of using pipelined functions in a *package*. I am not able to make it run within a package. I want to return an array of records from the pipelined function, say like name_1, address_1, city_1, hash value 1 name_2, address_2, city_2, hash value 2 .......... .......... Everywhere the examples given are of standalone functions. Also please refer to bug number 2136476 on Metalink regarding these pipelined functions. I desperately want to use such a wonderful feature. Thanks and regards Pratap

Hi tom, I want to know how Oracle parse and execute both static and dynamic SQL, can you explain to me ? Is it better to execute stored procedure than to use parse, execute SQL ? Thanks, Julien.

Hi Tom, Please clarify the following points: 1.what is the difference between local context and global context? 2.For a session, can both exists and if yes, what differentiates them? 3.I have heard that this concept is basically used for web based application because of the concept of connection pooling used by them.But why can't we have only one context. i.e whats the need of two context. In what condition/scenario can these be different? 4.Pointers to any online Oracle docs regarding this? Thanks Vinayak

Hello, We have created the multiple Editions in oracle 19c while dropping the editions using cascade we are getting below error. <code>ora-38810 implementation restriction cannot drop edition that has a parent and a child.</code> Request your help/suggestions to drop the editions. We have performed below steps to drop and edition. 1) Created below three editions. Using below command. <code>CREATE EDITION E1 AS CHILD OF ORA$BASE; EDITION_NAME PARENT_EDITION_NAME USABLE ORA$BASE YES E1 ORA$BASE YES E2 E1 YES E3 E2 YES</code> <code>alter session set edition =E1;</code> <code>Alter database default edition = E1;</code> Above all three steps performed for three editions and now current edition is set to E3; <code>select sys_context('USERENV','CURRENT_EDITION_NAME') from dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') E3 </code> <code>select * from database_properties where property_name = 'DEFAULT_EDITION'; Output: PROPERTY_NAME PROPERTY_VALUE DESCRIPTION DEFAULT_EDITION E3 Name of the database default edition </code> Now, We want to retire Edition E1 we performed below steps. 1) Checked the privilege's using below query. <code>SELECT *--GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'E1'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED IT7_HOST SYS E1 SYS USE YES NO NO EDITION NO </code> 2) Revoked privilege's of schema using below command. <code>REVOKE USE ON EDITION E1 FROM IT7_HOST; DROP EDITION E1 CASCADE;</code> Getting below error while dropping; <code>ora-38810 implementation restriction cannot drop edition that has a parent and a child </code> 4) dba_editions view shows below output. <code> EDITION_NAME PARENT_EDITION_NAME USABLE ORA$BASE YES E1 ORA$BASE YES E2 E1 YES E3 E2 YES </code> Regards GirishR

Hi, Business requirement is to generate excel/csv BIP output of concatenated transaction notes (on oracle cloud ERP). The identified source table is ZMM_NOTES, column NOTE_TXT (CLOB data type). Development team is using the listagg() function to concatenate multiple "note" entries for specific customer, code snippet below. <code>SELECT customer_trx_id ,transaction_note FROM (SELECT arpsa.customer_trx_id ,(SELECT listagg(t1.note_txt, '||- ') within group (ORDER BY t1.creation_date) FROM (SELECT note_txt ,source_object_code ,source_object_uid ,creation_date FROM zmm_notes WHERE source_object_code IN ('AR_TRANSACTION') AND source_object_uid = arpsa.customer_trx_id ) t1 ) AS transaction_note FROM ar_payment_schedules_all arpsa WHERE arpsa.status = 'OP' AND arpsa.customer_trx_id = 4800413 ) GROUP BY customer_trx_id ,transaction_note</code> The following errors are being encountered: <code>- ORA-01489: result of string concatenation is too long - ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1</code> It appears the concatenated string is then too long for an excel or cvs format. Any solution perhaps we are not aware of? thanks! Olga

Hi, I'm looking for a query to get all objects modified within the last 24 hours. The data dictionary has views like user_objects with usable columns (created, last_ddl_time, timestamp). But there are also views where I didn't find any information when the objects were modified or created. From my research these are: - user_tab_comments - user_col_comments - user_synonyms - user_tab_grants - user_code_grants Do you have any idea how to get the information when user created or modified comments, synonymms or grants??? Thanks, markus P.S. For performance reason I don't want to use complex queries against the audit tables, although there should be the information I'm looking for...

 A quick demonstration of the difference between the Table Statistic NUM_ROWS and the Index Statistic NUM_ROWS

SQL> desc customers
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 CUST_ID                                                                  NOT NULL NUMBER
 CUST_NAME                                                                         VARCHAR2(128)
 CUST_CITY                                                                         VARCHAR2(128)
 CUST_START_DATE                                                                   DATE

SQL> select index_name,  column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'CUSTOMERS'
  4  order by 1,2
  5  /

INDEX_NAME       COLUMN_POSITION COLUMN_NAME
---------------- --------------- ----------------
CUST_CITY_NDX                  1 CUST_CITY
CUST_ID_NDX                    1 CUST_ID

SQL>
SQL> select count(*) from customers;

  COUNT(*)
----------
   1299647

SQL>
SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL>
SQL> select last_analyzed, sample_size, num_rows
  2  from user_tables
  3  where table_name = 'CUSTOMERS'
  4  /

LAST_ANAL SAMPLE_SIZE   NUM_ROWS
--------- ----------- ----------
12-JUN-22     1299647    1299647

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>
SQL> select count(*)
  2  from customers
  3  where cust_city is null
  4  /

  COUNT(*)
----------
    119999

SQL>
SQL> select 1299647-1179648 from dual;

1299647-1179648
---------------
         119999

SQL>
SQL> create index cust_id_city_ndx
  2  on customers(cust_id, cust_city)
  3  /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_CITY_NDX 12-JUN-22     1299647    1299647
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>
SQL> drop index cust_id_city_ndx;

Index dropped.

SQL>
SQL> create index cust_city_id_ndx
  2  on customers(cust_city, cust_id)
  3  /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
  2  from user_indexes
  3  where table_name = 'CUSTOMERS'
  4  order by index_name
  5  /

INDEX_NAME       LAST_ANAL SAMPLE_SIZE   NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_ID_NDX 12-JUN-22     1299647    1299647
CUST_CITY_NDX    12-JUN-22     1179648    1179648
CUST_ID_NDX      12-JUN-22     1299647    1299647

SQL>

In recent versions, a CREATE INDEX implicitly includes a Gather Stats call on the new Index by default, so the two new Indexes also had updated statistics.

In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index). The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table. In this post, I’m going to […]

Working with a customer where we needed to move data from a Tandem (HP-UX Guardian) system up to an AWS […]

The post Initial Load from Tandem (HP-UX) to AWS Kafka appeared first on DBASolved.

Hi Tom, i get this error: SQL Error: ORA-00933-00000 - "SQL command not properly ended" when i run this query: <code>update a set propertyaddress = nvl2(a.propertyaddress, b.propertyaddress,b.propertyaddress) from nashvillehousing a join nashvillehousing b on a.parcelid = b.parcelid and a.uniqueid_ != b.uniqueid_ where a.propertyaddress is null</code> trying to populate property address where its null.

How to store old values as well as new values in the oracle table ?.there is one database block in which the user enters data in many columns. we need to track only two columns ( for eg) to store old value as well as new value. How to database trigger or how to perform in oracle forms itself? Your advice is appreciated. Regards and Thanks

Hi Mr Tom, I am posting a question first time on your site, hopefully will get an answer soon. I am trying to get hierarchy of a table using foreign key constraints. I want to pass a table name E.g. Customers and I want all children, grand children and so on of the table customers. Many thanks Haider

<i></i>Hi, Tom. I noticed strange behavior dbms_random.value using in sub-select. In LiveSQL example dbms_random.value is not unique for rows in sub-select. Oracle, under a certain meaningless condition (or 1!=0), transfers the ration column to the main select Because of this, one entry from <b>w_table1</b> can be returned for four entries from <b>w_table2</b> And also , records are inexplicably disappearing from w_table1 I found three ways to solve the problem: 1) MATERIALIZE for w_table1 2) Hint ORDERED in main select 3) Create a table using dbms_random before the select Is this some kind of bug? Or did I miss something when writing the query?

Hello Tom! We have a situation where we are using Java to access data in an Oracle database. Some of the objects are broken out into three tables as follows: * Primary table (e.g. MY_OBJECT) * Secondary table (e.g. MY_OBJECT_2) * Custom table (e.g. MY_OBJECT_C) The primary table contains core attributes that are frequently accessed for life cycle management, critical state information, etc. The secondary table contains core attributes that are infrequently accessed. The custom table contains attributes that are customer-specific. (This is a COTS product that is being developed.) We have a stored procedure with an output reference cursor parameter. Originally, it was written to only return the data in the primary table, such as the following: <code> CREATE OR REPLACE PROCEDURE my_procedure ( ... ... o_result_set OUT SYS_REFCURSOR ) AS BEGIN -- Initialize the output o_result_set := NULL; ... ... OPEN o_result_set FOR SELECT * FROM my_object WHERE ... ...; END; </code> The unit test for this procedure used a %ROWTYPE designator for the variable to fetch the result sets into such as the following: <code> DECLARE v_my_object_records SYS_REFCURSOR; v_my_object_record my_object%ROWTYPE; BEGIN -- Call the procedure my_procedure( v_my_object_records ); -- Loop through the resulting data IF (v_my_object_records IS NOT NULL) THEN LOOP FETCH v_my_object_records INTO v_my_object_record; EXIT WHEN v_my_object_records%NOTFOUND; DBMS_OUTPUT.PUT_LINE('* ' || v_my_object_record.object_id); END LOOP; CLOSE v_my_object_records; END IF; END; </code> This works great. As you can see, the result set is fetched into a variable defined as type my_object%ROWTYPE. However, the need arose for returning one of the columns in the secondary table. Since this is a COTS framework, and the custom attributes (columns) are not known at design time of the stored procedure, we decided to extend the stored procedure to also return the secondary and custom attributes in a manner such as follows: <code> CREATE OR REPLACE PROCEDURE my_procedure ( ... ... o_result_set OUT SYS_REFCURSOR ) AS BEGIN -- Initialize the output o_result_set := NULL; ... ... OPEN o_result_set FOR SELECT m.*, m2.*, mc.* FROM my_object m INNER JOIN my_object_2 m2 ON m.object_id = m2.object_id INNER JOIN my_object_c mc ON m.object_id = mc.object_id WHERE ... ...; END; </code> This procedure compiles fine and even appears to work fine in the Java code. However, we are not able to produce a PL/SQL unit test for this because there is no clean "ROWTYPE" to define for the output in this instance, since the records do not correspond to a row in a table. <b> Question: Is there a way to process this kind of result set variable within PL/SQL? </b> I much prefer to use PL/SQL for the unit test of the stored procedure. Note that we cannot define a data type with a composite of the table columns and rely on this for the product, since this is a framework and the custom attributes are not known at design time. We did try to create one just for unit test purposes like the following: <code> DECLARE TYPE my_object_record_type IS RECORD ( my_o...

1) I created Employee master FORM from emp table 2) In employee form deptno is the select list and I put button for new department add. It will call dept page form which has property chained = false 3) After adding new dept control goes back to employee form but I can find newly added dept in list 4) test case created on apex.oracle.com [redacted] Page no:-2 model page of employee call from page no 1 new. Page no:- 3 model page of dept call from page no 2 new button next to dept list of value Application export : https://drive.google.com/file/d/1IRFpTMuI-b_ndmbwDjahds8DGk4_zEHQ/view?usp=sharing Test-video : https://drive.google.com/file/d/1_QrRcXngwqQ39r5Bp_4aWttIEG3Hpc-o/view?usp=sharing

I have two questions both referring to the documentation in Oracle 19c SQL Language Reference PARALLEL hint "For a statement-level PARALLEL hint" section. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D25225CE-2DCE-4D9F-8E82-401839690A6E 1) PARALLEL and PARALLEL (AUTO) have the exact same definition, but the two examples that immediately follow in the documentation describe different behavior. Namely, PARALLEL will always run in parallel while the preceding definition says a statement with PARALLLEL hint may run serially. Please clarify this seeming contradiction. 2) PARALLEL and PARALLEL (AUTO) state "...the computed degree of parallelism,...", but do not specify the computation. I was suspecting it is DOP = PARALLEL_THREADS_PER_CPU * CPU_COUNT (for single instance), but the definition of PARALLEL (DEFAULT) provides that calculation. It would be misleading for all three PARALLEL, PARALLEL (AUTO), and PARALLEL (DEFAULT) to use the same calculation but only to define the calculation for one. Hence, I now suspect the calculation for PARALLEL and PARALLEL (AUTO) is something else. What is the calculation(s) used for DOP for PARALLEL and PARALLEL (AUTO) hint? Thank you in advance.

What query can be used to find the duplicates using a group of columns among which one is a varchar type from a million of records. The varchar type column matching must be case insensitive and space insensitive. In the attached data all rows corresponds to the same name but the group by or aggregate treats them as different record. The grouping should be case insensitive and ignore the spaces. I need to perform the operation on a million records. create table duplicate_filter(name varchar2(50), age integer, salary number) ; insert into duplicate_filter(name, age, salary) values ('John Doe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('JohnDoe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('john Doe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('johndoe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('john doe', 20, 100) ; commit select name, age, salary , count(1) total from duplicate_filter group by name, age ,salary ; select name, age, salary, count(1) over(partition by name, age, salary) total from duplicate_filter ;

I have a DB on ExaCC Gen1 platform with a big LOB segment and when I try to compress it I ran into undo problems. Table size 2,68G LOB segment size 14TB UNDOTBS1 size 544G UNDOTBS2 size 128G Sys@DBname1> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 3000000 Sys@DBname1> set timing on Sys@DBname1> ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online; ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old Elapsed: 75:11:23.77 I am played around with undo_retention parameter, however this seems not help me in this matter. What else could I try in order to overcome this issue? Additional information: CREATE TABLE "UCMMASTER"."FILESTORAGE" ( "DID" NUMBER(*,0) NOT NULL ENABLE, "DRENDITIONID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DLASTMODIFIED" TIMESTAMP (6), "DFILESIZE" NUMBER(*,0), "DISDELETED" VARCHAR2(1 CHAR), "BFILEDATA" BLOB, CONSTRAINT "PK_FILESTORAGE" PRIMARY KEY ("DID", "DRENDITIONID") USING INDEX 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 "UCMMASTER" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 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 "UCMMASTER" INMEMORY PRIORITY HIGH MEMCOMPRESS FOR QUERY LOW DISTRIBUTE AUTO DUPLICATE ALL LOB ("BFILEDATA") STORE AS SECUREFILE ( TABLESPACE "UCMMASTER" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) CACHE ENABLE ROW MOVEMENT