Oracle Partition Insert/Update Metadata?

As an optimisation strategy we're looking into using Oracle Partition Metadata to identity which partitions have had rows inserted/updated in the last 24 hours. We want to process the changed partitions only. Would anyone know if such metadata exist...
more »

2017-03-21 17:03 (2) Answers

ORA-00907 Missing Right Parenthesis (Oracle)

I was previously holding my data in SharePoint. At that time, the below query ran fine :- SELECT Nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect FROM tblQuotesNew AS t1; Now that I have moved my data to Oracle (but still retrieving it ...
more »

2017-03-21 17:03 (1) Answers

Oracle Flashback on System Tables

Is there a possibility to use the Oracle Flashback on System tables, such as the user_tables or cols? I was able to successfully issue Flashback queries against the tables I created, but when I tried the same against System tables, I get ORA-01031: ...
more »

2017-03-21 11:03 (1) Answers

Oracle 10g - convert columns to string

I have a table with x columns and i need drop 4 of them, but before i need transform these columns into varchar in json format. Example how is now car ----------------------------- id name color year HP -- ------ ------ ---- --- 1 po...
more »

2017-03-20 19:03 (1) Answers

Create object type with rowid attributes

I am trying to create a type with the rowid data type, but I am getting this error because of the type I am trying to use: SQL> CREATE TYPE join_t IS OBJECT (inn rowid, out rowid ); / Warning: Type created with compilation errors. Even th...
more »

2017-03-20 16:03 (1) Answers

Can't insert ñ character to an Oracle database

I've got an issue inserting ñ chatacter to an Oracle database. The INSERT operation completes successfully. However, when I SELECT, I get n instead of ñ. Also, I noticed executing: select 'ñ' from dual; gives me 'n'. select * from nls_datab...
more »

2017-03-20 15:03 (3) Answers

SQL REGEX search for table name

this is my first entry, so please excuse me for any mistakes I might make writing this Post. I have an oracle database schema full of Table Views. I want to use the "TEXT_VC" column in the "all_views" table to get a list of the source table names. ...
more »

2017-03-20 10:03 (1) Answers

Why does this UPDATE result in a deadlock?

I have a table called unprocessed that gets filled with rows around the clock. It looks something like this CREATE TABLE unprocessed ( id INT PRIMARY KEY, name VARCHAR2(10), qty INT, thread_id VARCHAR2(30) ); -- This indexes NULL v...
more »

2017-03-19 23:03 (0) Answers

SQL - Select instance type

I am using Oracle DB and I have this type: CREATE TYPE t_foo AS OBJECT (a VARCHAR2(20)) NOT FINAL; And also these subtypes CREATE TYPE t_foo1 UNDER t_foo() NOT FINAL; CREATE TYPE t_foo2 UNDER t_foo() NOT FINAL; I have a table of type t_foo wher...
more »

2017-03-19 16:03 (1) Answers

How to work in case in join condition

How to find city when ContactID is provided and condition is if ContactID is coming as 123 then it will look whether it is P or C, If P then it will go to Person table and returns City(USA) as output and If C then it will go to Company table and gi...
more »

2017-03-19 16:03 (3) Answers