DBA Blogs

Archive Table data and restore when needed

Tom Kyte - Tue, 2018-08-28 13:06
We have a table named DS_AUDIT_ENTRY where logs of audits are recorded. It contains many years data. Now we want to keep 6 months data only and archive old data. Also if required we have to fetch the old data whenever asked. How can I perform this a...
Categories: DBA Blogs

Loading PDFbox jars to oracle DB.

Tom Kyte - Tue, 2018-08-28 13:06
Hi, I am trying to add specific text to a pdf file using the apache pdfbox utility. I have loaded following jars on to DB using following command. loadjava -verbose -u user/pwd@ccoadev -jarasresource -genmissing -grant public -resolve pdfbox...
Categories: DBA Blogs

Getting JSON from odata into json table, with a special character '@'

Tom Kyte - Tue, 2018-08-28 13:06
Hello Tom, The odata-JSON string created by f/e https://www.odata.org/getting-started/ what is provided through a web-service needs to be inserted into a table. The json looks like: {"@odata.context": "serviceRoot/$metadata#People"} How can i ext...
Categories: DBA Blogs

INST_DRTLD_MISMATCH cause version count very high

Tom Kyte - Tue, 2018-08-28 13:06
Hi, Recently I faced a problem , my 9i database had a latch free wait event on statpack report I found a SQL consume almost full of my shared pool All of the hash value are same ,So I query v$sql_shared_cursor view to find out why T...
Categories: DBA Blogs

Performance issues with Views

Tom Kyte - Tue, 2018-08-28 13:06
Hi, I have a situation where I have created multiple views to control the access on tables using roles of users. And now users are writing queries on top of multiple complex views, And facing performance issues. Is there anyways to avoid views and go...
Categories: DBA Blogs

Temporary Tables

Tom Kyte - Tue, 2018-08-28 13:06
Hi All, I'm a recent convert from PL/SQL to SQL Developer & Oracle. As such, I used #temptables a ton and was highly discouraged from writing 'super SQL' scripts that tried to do everything in one query. Now, I'm learning that just the opposit...
Categories: DBA Blogs

Tunning Between clause

Tom Kyte - Mon, 2018-08-27 18:46
i am trying to tune a query which contains between clause in Oracle 11g. i have table employee(id number, join_dt date, end_dt date) which has 10 million records. and it has index on join_dt,end_dt first run, dbms_stats.gather_table_stats(owne...
Categories: DBA Blogs

FOPEN to sub folders

Tom Kyte - Mon, 2018-08-27 18:46
Hello, I am trying to find a way to write a file into the sub folder of an Oracle Directory. I can write into the base of the oracle directory but not into the sub folders. To keep it simple, This is what we have that currently works, after that i...
Categories: DBA Blogs

ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], [],

Tom Kyte - Mon, 2018-08-27 18:46
Hi Tom, Our database is oracle 11.2.0.3. My customer met an error "ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], []," when he did 'select * from UPL_SECTOR'. UPL_SECTOR is a table he created by himself...
Categories: DBA Blogs

Move historical data between databases

Tom Kyte - Mon, 2018-08-27 18:46
Hello Tom See how you could optimize moving records (historical by date) from one table in a production database to another table in another historical database in an automatic way. Could you support me in Oracle Partition? It could be used ex...
Categories: DBA Blogs

Partitioning -- 3c : Unique Index[es] on Partitioned Table

Hemant K Chitale - Sun, 2018-08-26 03:49
Let's explore what sort of Unique Indexes you can create on a Partitioned Table.

There are three types of partitioning for Indexes :

a  Global (Non-Partitioned)

b  Global Partitioned

c  Local Partitioned

Can a Unique Index be created using either type ?

Let me start with another table, SALES_DATA_2  which has the same structure and Partition Key as SALES_DATA, except that it doesn't have the Primary Key definition that builds the Unique Index.

SQL> l
1 CREATE TABLE SALES_DATA_2
2 ( SALE_ID NUMBER,
3 SALE_DATE DATE,
4 INVOICE_NUMBER VARCHAR2(21),
5 CUSTOMER_ID NUMBER,
6 PRODUCT_ID NUMBER,
7 SALE_VALUE NUMBER
8 )
9 TABLESPACE HEMANT
10 PARTITION BY RANGE (SALE_DATE)
11 (PARTITION P_2018 VALUES LESS THAN (TO_DATE(' 2019-01-01','YYYY-MM-DD'))
12 TABLESPACE TBS_YEAR_2018 ,
13 PARTITION P_2019 VALUES LESS THAN (TO_DATE(' 2020-01-01','YYYY-MM-DD'))
14 TABLESPACE TBS_YEAR_2019 ,
15 PARTITION P_2020 VALUES LESS THAN (TO_DATE(' 2021-01-01','YYYY-MM-DD'))
16 TABLESPACE TBS_YEAR_2020 ,
17 PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
18* TABLESPACE HEMANT )
SQL> /

Table created.

SQL>


Next, I try a Global (Non-Partitioned) Unique Index on SALE_ID.  Note that the "GLOBAL" Keyword is optional if it is Non-Partitioned.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) global
3 tablespace hemant
4 /

Index created.

SQL>
SQL> select partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

PAR STATUS
--- --------
NO VALID

SQL> drop index sales_2_uk;

Index dropped.

SQL>


Effectively, this Global Index is the same as the Primary Key index on SALES_DATA that I built earlier.

Next, I try a Unique Global Partitioned Index on the same column.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) global
3 partition by range (sale_id)
4 (partition p_1mill values less than (1000001) tablespace new_indexes,
5 partition p_2mill values less than (2000001) tablespace new_indexes,
6 partition p_3mill values less than (3000001) tablespace new_indexes,
7 partition p_maxval values less than (maxvalue) tablespace new_indexes)
8 /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE YES N/A

SQL>
SQL> l
1 select column_position, column_name
2 from user_part_key_columns
3 where name = upper('sales_2_uk')
4* order by column_position
SQL> /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_ID

SQL>
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = upper('sales_2_uk')
4 order by partition_position
5 /

PARTITION_NAME STATUS
------------------------------ --------
P_1MILL USABLE
P_2MILL USABLE
P_3MILL USABLE
P_MAXVAL USABLE

SQL>


So, that is a valid Unique Global Partitioned Index.

The next attempt is a Unique Local Partitioned Index -- i.e. partitioned by the same key as the Table.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) local
3 /
on sales_data_2 (sale_id) local
*
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


SQL> !oerr ora 14039
14039, 00000, "partitioning columns must form a subset of key columns of a UNIQUE index"
// *Cause: User attempted to create a UNIQUE partitioned index whose
// partitioning columns do not form a subset of its key columns
// which is illegal
// *Action: If the user, indeed, desired to create an index whose
// partitioning columns do not form a subset of its key columns,
// it must be created as non-UNIQUE; otherwise, correct the
// list of key and/or partitioning columns to ensure that the index'
// partitioning columns form a subset of its key columns

SQL>
SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id, sale_date) local
3 /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE YES N/A

SQL> select column_position, column_name
2 from user_part_key_columns
3 where name = upper('sales_2_uk')
4 order by column_position
5 /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_DATE

SQL> select column_position, column_name
2 from user_ind_columns
3 where index_name = upper('sales_2_uk')
4 order by column_position
5 /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_ID
2 SALE_DATE

SQL>
SQL> select partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name = upper('sales_2_uk')
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
P_2018 TBS_YEAR_2018 USABLE
P_2019 TBS_YEAR_2019 USABLE
P_2020 TBS_YEAR_2020 USABLE
P_MAXVALUE HEMANT USABLE

SQL>


So, a Unique Local Partitioned Index must include the Table Partition Key as a subset of the Index Key columns.  This is something you must consider when Partitioning the Table and Index both.
(Also, note how USER_PART_KEY_COLUMNS doesn't show SALE_ID as a Partition Key.  This is in 11.2.0.4)



Categories: DBA Blogs

Spreadsheet Upload

Tom Kyte - Fri, 2018-08-24 17:26
Hi there, If there is a way to upload the spreadsheet data in our existing application? If possible please send your answers. Regards, Aravindan Prem
Categories: DBA Blogs

JET Line Chart - Step Handling

Tom Kyte - Fri, 2018-08-24 17:26
I have a problem generating the vertical lines in a line chart. E.g. take this query: with nums as ( select rownum as rnum from dual connect by rownum < 300) select rnum/9 as x, sin(2*rnum/30) as y from nums In the X-Axis, my tick mar...
Categories: DBA Blogs

New Zealand: “Oracle Indexing Internals and Best Practices” Seminars November 2018.

Richard Foote - Thu, 2018-08-23 17:37
Good news for those of you in beautiful New Zealand. Due to popular demand, I’ll be returning to run some of my acclaimed “Oracle Indexing Internals and Best Practices” seminars in November 2018. The dates and events are: Wellington: 19-20 November 2018: Registration Here or Buy Directly Here Auckland: 21-22 November 2018: Registration Here or […]
Categories: DBA Blogs

Doing joins on denormalized data in tables

Tom Kyte - Wed, 2018-08-22 10:26
Hi Team RDBMS version: 12.2 OEL: 7.5 Trying to evaluate new table structure to query data, which I've heard is very widely used these days in e-commerce companies at scale. Though most of those companies might be using NoSQL solutions for the ...
Categories: DBA Blogs

Get difference between 2 date columns in HH:MM:SS

Tom Kyte - Wed, 2018-08-22 10:26
I am trying to get difference between 2 date columns in HH:MM:SS. Currently I have the below query which is returning the difference in HH:MM:SS. The problem with the below query is even if there is a difference of 1 day and 13 hours, it is just givi...
Categories: DBA Blogs

Documentation claims that multiset conditions also work with varrays, which they don't

Tom Kyte - Wed, 2018-08-22 10:26
According to the documentation, a lot of multiset conditions should also work with varrays, which they don't (at least not in the SQL language): https://docs.oracle.com/database/121/SQLRF/conditions006.htm#SQLRF52138 I've created a Stack Overflow...
Categories: DBA Blogs

QUERY CLAUSE for 2 join tables

Tom Kyte - Wed, 2018-08-22 10:26
Hi Tom, I have 2 SQL scripts giving lots of rows :- ---Export Data for fy17 filename: w_camp_hist_f_2016.dmp select count(*) from olap.w_camp_hist_f,olap.w_day_d where w_camp_hist_f.ld_dt_wid=w_day_d.row_wid and w_day_d.petc_year = 2016; ...
Categories: DBA Blogs

Question from Office Hours on Data Visualization with Oracle APEX

Tom Kyte - Wed, 2018-08-22 10:26
Hi, Just wanted to check if there will there a recording of this available ?
Categories: DBA Blogs

Duplicate columns in Oracle query using row limiting clause

Tom Kyte - Tue, 2018-08-21 16:06
Since Oracle 12c, we can finally use the SQL standard row limiting clause like this: <code>SELECT * FROM t FETCH FIRST 10 ROWS ONLY</code> Now, in Oracle 12.1, there was a limitation that is quite annoying when joining tables. It's not possible...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs