DBA Blogs

Is UTL_MAIL supported in 11g EE

Tom Kyte - Sat, 2017-09-30 17:46
Hi Team, Wanted to know wherher utl_mail is supported in 11g EE.I installed the version in my local windows machine.I am able to connect to it via SQL Developer. I can see utl_smtp and utl_tcp packages are installed. I tried to install the utlmail...
Categories: DBA Blogs

What performs better NVL or DECODE for evaluating NULL values

Tom Kyte - Fri, 2017-09-29 23:26
Afternoon, Could anyone tell me which of the following statements would perform better? <code> SELECT 1 FROM DUAL WHERE NVL (NULL, '-1') = NVL (NULL, '-1') </code> OR <code> SELECT 1 FROM DUAL WHERE DECODE(NULL, NULL, '1', '0') = '...
Categories: DBA Blogs

Which Index is Better Global Or Local in Partitioned Table?

Tom Kyte - Fri, 2017-09-29 23:26
We have partitioned table based on date say startdate (Interval partition , For each day) We will use query that will generate report based on days (like report for previous 5 days) Also we use queries that will generate report based on hours (li...
Categories: DBA Blogs

SQL to find the ip address

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom, I want to capture the IP address of any client who has shutdown the db. Support currently in my db 5 clients are connected, one client shutdown the db, then I want to capture the IP address of client who has been shutdown the db. How to solv...
Categories: DBA Blogs

AWR

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom/Team, I am aware of the definition of terms used in AWR Report. but i want to know that - how to calculate and on what basis we need to calculate values listed for points in below 2 section of AWR report 1. Top 5 timed foreground event ...
Categories: DBA Blogs

BInary operator like AND, XOR

Tom Kyte - Fri, 2017-09-29 23:26
Hi i have a simple question can we use the binary operator like AND or XOR in a SQL statement. For example "select 1 AND 1 from dual;" result = 1 or true or "select 1 XOR 1 from dual;" if not, please can you tell me how can i do to have th...
Categories: DBA Blogs

Lots of archivelog generation when shrinking and compacting segments

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom, 1. what is the reason of huge redo and archivelog generation when compacting and shriking huge segments in 10g? 2. How it can be avoided or minimized? Thanks JP
Categories: DBA Blogs

Partitioned Indexes

Hemant K Chitale - Fri, 2017-09-29 10:15
Most discussions about Partitioning in Oracle are around Table Partitioning.  Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.

So, here is a quick listing of Index Partitioning options  (these tests are in 11.2.0.4)


First, I start with a regular, non-partitioned table.

SQL> create table non_partitioned  
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 /

Table created.

SQL>


I now attempt to create an Equi-Partitioned (LOCAL) Index on it.

SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
*
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned


SQL>


As expected I can't create a LOCAL index on a non-partitioned table.

Can I create any partitioned index on this table ?

I try two different GLOBAL PARTITIONed Indexes

SQL> create index global_part   
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201)
6 )
7 /
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns


SQL>
SQL> create index global_part
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /

Index created.

SQL>
SQL> create index global_part_comp
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (id_col, data_col_3)
4 (partition p_1 values less than (101,'M'),
5 partition p_2 values less than (101,MAXVALUE),
6 partition p_3 values less than (201,'M'),
7 partition p_4 values less than (201,MAXVALUE),
8 partition p_max values less than (MAXVALUE, MAXVALUE)
9 )
10 /

Index created.

SQL>


So, I must have a MAXVALUE partition for the Index.  Note that the two indexes above are now Partitioned without the table itself being partitioned.

SQL> select index_name, partitioned
2 from user_indexes
3 where table_name = 'NON_PARTITIONED'
4 order by 1
5 /

INDEX_NAME PAR
------------------------------ ---
GLOBAL_PART YES
GLOBAL_PART_COMP YES

SQL>


The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.

SQL> create index global_part_nonprefix
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed

SQL>


So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.

Let me now proceed with a Partitioned Table.

SQL> create table partitioned
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 partition by range (id_col)
8 (partition p_100 values less than (101),
9 partition p_200 values less than (201),
10 partition p_max values less than (MAXVALUE)
11 )
12 /

Table created.

SQL>


First, the Equi-Partitioned (LOCAL) Index.

SQL> create index part_equi_part
2 on partitioned (id_col) local
3 /

Index created.

SQL> select partition_name, partition_position
2 from user_ind_partitions
3 where index_name = 'PART_EQUI_PART'
4 order by 2
5 /

PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------
P_100 1
P_200 2
P_MAX 3

SQL>


The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table.  Index Partitions are automatically created to match the Table Partitions with the same Partition Names.  It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea.  Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.

Next, I define two GLOBAL Partitioned Indexes on this table.

SQL> create index part_gbl_part  
2 on partitioned (data_col_1) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (1001),
5 partition p_2 values less than (2001),
6 partition p_3 values less than (3001),
7 partition p_4 values less than (4001),
8 partition p_max values less than (MAXVALUE)
9 )
10 /

Index created.

SQL> create index part_gbl_part_comp
2 on partitioned (data_col_2, data_col_3) global
3 partition by range (data_col_2, data_col_3)
4 (partition p_a values less than (10, 'M'),
5 partition p_b values less than (10, MAXVALUE),
6 partition p_c values less than (20, 'M'),
7 partition p_d values less than (20, MAXVALUE),
8 partition p_e values less than (30, 'M'),
9 partition p_f values less than (30, MAXVALUE),
10 partition p_max values less than (MAXVALUE, MAXVALUE)
11 )
12 /

Index created.

SQL>
SQL> l
1 select index_name, partition_name, partition_position
2 from user_ind_partitions
3 where index_name in
4 (select index_name from user_indexes
5 where table_name = 'PARTITIONED'
6 )
7* order by 1,3
SQL> /

INDEX_NAME PARTITIO PARTITION_POSITION
------------------ -------- ------------------
PART_EQUI_PART P_100 1
PART_EQUI_PART P_200 2
PART_EQUI_PART P_MAX 3
PART_GBL_PART      P_1                       1
PART_GBL_PART P_2 2
PART_GBL_PART P_3 3
PART_GBL_PART P_4 4
PART_GBL_PART P_MAX 5
PART_GBL_PART_COMP P_A                       1
PART_GBL_PART_COMP P_B 2
PART_GBL_PART_COMP P_C 3
PART_GBL_PART_COMP P_D 4
PART_GBL_PART_COMP P_E 5
PART_GBL_PART_COMP P_F 6
PART_GBL_PART_COMP P_MAX 7

15 rows selected.

SQL>


The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.

As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not  a left-prefix of the Index).

SQL> create index part_global_part_nonprefix
2 on partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>


In this blog post, I haven't touched on Partial Indexing (a 12c feature).

I haven't touched on Unique LOCALly Partitioned Indexes.

I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.

Categories: DBA Blogs

Calling Procedure Parallel

Tom Kyte - Fri, 2017-09-29 05:06
I have below procedure which in turn calls two other Procedures. It calls and works fine but the two procs runs serial. I want to run them parallel and get the results on the main procs cursor. How do I do that? I tried with dbms_job.submit but could...
Categories: DBA Blogs

Getting sub-string from two Clobs object and compare those substrings

Tom Kyte - Fri, 2017-09-29 05:06
Hi, I am new to CLOB objects but seems like I need to get my hands dirty on this. I have a CLOB column in my table and I need to get item SKU values from this column separated by commas. This is hoe my CLOB Column value looks like. ------- <...
Categories: DBA Blogs

External table concepts

Tom Kyte - Fri, 2017-09-29 05:06
Hi All, I am new to oracle external table concepts. Have a very basic query - if i have a csv with the below columns Col1, Col2, Col3 Col4 .... Coln and i want to insert only Col3 & Col4 into an oracle external table , what would be my ...
Categories: DBA Blogs

sql query to update a table based on data from other table

Tom Kyte - Fri, 2017-09-29 05:06
Hi, Looks like my other similar questions got closed, so asking a new question. I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER N...
Categories: DBA Blogs

Updating records with many-to-1 linked table relationship

Tom Kyte - Thu, 2017-09-28 10:46
I have an MS_ACCESS Query to convert to Oracle SQL. Access Query <code>UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field3 = S.field3;</code> Note: T...
Categories: DBA Blogs

Native dynamic sql - Refcursor

Tom Kyte - Thu, 2017-09-28 10:46
Tom, Here is an example...that i want to change one function to avoid redundant information. create or replace package p_ref_cursor is type ret_ref_cursor is ref cursor; end p_ref_cursor; / drop table "tab1"; create table "tab1" ...
Categories: DBA Blogs

ORA-06502 with CHAR parameter. What am I missing?

Tom Kyte - Thu, 2017-09-28 10:46
Sorry to bother you with a ORA-06502 error. But I'm not understanding this behavior. As I saw, the length fits (see the dbms_output in result showing that the length is 16). The only thing i can think is that in the procedure proc, pl/sql is...
Categories: DBA Blogs

ORA-00060 : Deadlock detected while waiting for resource in Multi-threaded Java Batch process

Tom Kyte - Wed, 2017-09-27 16:26
Hi Tom I have a multi-threaded batch process running on Production that fails due to "ORA-00060 : Deadlock detected while waiting for resource". I am getting following error message: <code>02:25:25,899 [CobolThread 34] ERROR Error executing upd...
Categories: DBA Blogs

Materialized Views: Refresh Statistics History - How can I see data in user_mvref_stmt_stats

Tom Kyte - Wed, 2017-09-27 16:26
Hi Masters, sorry for another question :-) and again a big, big thank You for Your hard work and the answers to our questions!! Could You please have a look into my test case? Some of the statement fail, because I do not have all the privs in l...
Categories: DBA Blogs

Using JSON_TABLE with aggregate functions

Tom Kyte - Wed, 2017-09-27 16:26
Hi Team DB Version: 12.2.0.0.3 On a high level, on successful completion of the scripts (from LiveSQL) you would have: 1) Loaded 10k users data in JSON format in STAGE_USER table. 2) Loaded 500 roles data in JSON format in STAGE_ROLE table. ...
Categories: DBA Blogs

ORA-00937 for aggregate in scalar subquery

Tom Kyte - Wed, 2017-09-27 16:26
<code>HI CAN ANY BODY EXPLAIN IN 1ST QUERY WHY IT IS ERROR BUT IN 2ND QUERY IT GIVES RESULT ? SQL> SELECT SUM(SAL), 2 ( 3 SELECT SUM(SAL) FROM EMP 4 ) 5 FROM EMP; SELECT SUM(SAL) FROM EMP * ERROR at line 3: ORA-00937: n...
Categories: DBA Blogs

Partner Webcast – Understanding GDPR and the Oracle value proposition for Partners

The EU General Data Protection Regulation (GDPR), replaces the over fifteen year old existing EU Data Protection Directive, and it’s equally important for Oracle customers and partners. While...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs