DBA Blogs

advantage of procedure within a procedure

Tom Kyte - Thu, 2018-11-01 09:26
I've seen package code where there are procedures defined within a procedure, then the "within" procedure is called once in the main procedure - actually I've also seen 3 "within" procedures and the only thing the main procedure does is call these 3 ...
Categories: DBA Blogs

Oracle Fact Table creation and loading strategy

Tom Kyte - Wed, 2018-10-31 15:06
Thanks in advance for the support.I have a few questions: 1. I will have to load 7 years worth of history data with 128 billion rows into a new Fact Table, which also will have inserts as well as updates for the last 90 days (98% inserts and 2% up...
Categories: DBA Blogs

ORDER BY and CONSISTENT GETS divided by ten

Tom Kyte - Wed, 2018-10-31 15:06
Hello TOM, When I use an ORDER BY, I see that the consistent gets are divided by ten in my tests, can you explain why? Here are my tests. First I create a table. <code> SQL> create table test_obj01 as select OWNER, OBJECT_NAM...
Categories: DBA Blogs

Ordering serializable transactions

Tom Kyte - Wed, 2018-10-31 15:06
Hi, I'm trying to work out how to assign an ordering to the results of potentially concurrent serializable transactions so I can definitively say either: - They both had an identical view of the database, or - Identify which one had a later view...
Categories: DBA Blogs

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part IV” (Hallo Spaceboy)

Richard Foote - Tue, 2018-10-30 22:17
In Part I, Part II and Part III we looked at some advantages of Global Indexes that may not be obvious to some. One of the advantages of a Local Index vs. Non-Partitioned Global Index is that a Local Index being a smaller index structures may have a reduced BLEVEL in comparison. This can save […]
Categories: DBA Blogs

Spatial queries involving lattitude and longitude

Tom Kyte - Tue, 2018-10-30 02:26
Team, We have a table in an application that has longitude and lattitue as NUMBER datatype. also composite index exists on the columns (LATITUDE, LONGITUDE) in this order. the sql from the application goes like this. select * from the_tabl...
Categories: DBA Blogs

Partitioning - 7 : Interval Partitioning

Hemant K Chitale - Sat, 2018-10-27 01:32
Interval Partitioning was introduced in 11g as an enhancement to Range Partitioning, but supporting only DATE and NUMBER datatypes.  This allows you to define the interval for each Partition and leave it to the database engine to automatically create new Partitions as required when data is inserted.  Thus, you do not have to pre-create Partitions for future data.

Here is a demo with Monthly Date Intervals.

 2  (manufacture_date date,
3 item_code varchar2(32),
4 item_quantity number(8,0))
5 partition by range (manufacture_date)
6 interval (numtoyminterval(1,'MONTH'))
7 (partition P_1 values less than (to_date('01-JUL-2018','DD-MON-YYYY')))
8 /

Table created.

SQL> set long 32
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',

SQL>


The INTERVAL clause specifies how the upper bounds for new Partitions are to be defined.  I only need to name the boundary for the first (lowest) Partition and name the Partition.  All subsequent Partitions are automatically created with names assigned by Oracle and high values based on the INTERVAL clause.

Let me insert a few rows.

SQL> insert into manufacturing_summary
2 (manufacture_date, item_code, item_quantity)
3 values
4 (to_date('29-JUN-2018','DD-MON-YYYY'), 'ABC123',4000)
5 /

1 row created.

SQL> insert into manufacturing_summary
2 values (to_date('01-JUL-2018','DD-MON-YYYY'),'ABC123',3000)
3 /

1 row created.

SQL> insert into manufacturing_summary
2 values (to_date('01-JUL-2018','DD-MON-YYYY'),'FGH422',1000)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',
SYS_P519 TO_DATE(' 2018-08-01 00:00:00',

SQL>


Oracle automatically created Partition S_P519 for July data.

What happens if there manufactuing daa is not available from 02-Jul-2018 to, say, 04-Sep-2018 ?  And availability of data resumes only on 05-Sep-2018 ?

SQL> insert into manufacturing_summary
2 values (to_date('05-SEP-2018','DD-MON-YYYY'),'ABC123',3000)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
1 P_1
TO_DATE(' 2018-07-01 00:00:00',

2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

3 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL>


The third Partition, SYS_P520 is created with the Upper Bound (HIGH_VALUE) of 01-Oct for the September data.

What if August data becomes available subsequently and is inserted ?

SQL> insert into manufacturing_summary
2 values (to_date('10-AUG-2018','DD-MON-YYYY'),'ABC123',1500)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
1 P_1
TO_DATE(' 2018-07-01 00:00:00',

2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL>


A new Partition with the HIGH_VALUE of 01-Sept did get created as SYS_P521 and inserted into the ordered position 3.  While the previously created Partition S_P520 (HIGH_VALUE 01-Oct) got renumbered to 4.  We can verify this by actually querying the Partitions.

SQL> select * from manufacturing_summary partition (SYS_P521);

MANUFACTU ITEM_CODE ITEM_QUANTITY
--------- -------------------------------- -------------
10-AUG-18 ABC123 1500

SQL>
SQL> select * from manufacturing_summary partition (SYS_P520);

MANUFACTU ITEM_CODE ITEM_QUANTITY
--------- -------------------------------- -------------
05-SEP-18 ABC123 3000

SQL>


SYS_P520 was created first for September data although no August data existed.  SYS_P521 was created subsequently for August data which was inserted later.

Remember this : NEVER rely on Partition Names to attempt to identify what data is in a Partition.  Always use PARTITION_POSITION and HIGH_VALUE to identify the logical position (rank) and the data that is present in the Partition.

Where do the Partition names SYS_P519, SYS_P520, SYS_P521 come from ?  They are from a system defined sequence, self-managed by Oracle.

Let me demonstrate this with another example.

SQL> l
1 create table dummy_intvl_tbl
2 (id_col number,
3 data_col varchar2(15))
4 partition by range(id_col)
5 interval (100)
6* (partition P_1 values less than (101))
SQL> /

Table created.

SQL> insert into dummy_intvl_tbl
2 values (50,'data1');

1 row created.

SQL>
SQL> insert into dummy_intvl_tbl
2 values (150,'data3');

1 row created.

SQL>
SQL> insert into manufacturing_summary
2 values (to_date('25-OCT-2018','DD-MON-YYYY'),'FGH422',500);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select table_name, partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name in ('MANUFACTURING_SUMMARY','DUMMY_INTVL_TBL')
4 order by 1,2
5 /

TABLE_NAME PARTITION_POSITION PARTITION_NAME
------------------------------ ------------------ ------------------------------
HIGH_VALUE
--------------------------------
DUMMY_INTVL_TBL 1 P_1
101

DUMMY_INTVL_TBL 2 SYS_P525
201

MANUFACTURING_SUMMARY 1 P_1
TO_DATE(' 2018-07-01 00:00:00',

MANUFACTURING_SUMMARY 2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

MANUFACTURING_SUMMARY 3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

MANUFACTURING_SUMMARY 4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',

MANUFACTURING_SUMMARY 5 SYS_P526
TO_DATE(' 2018-11-01 00:00:00',


7 rows selected.

SQL>


Note how Partition Name SYS_P525 was allocated to DUMMY_INTVL_TBL and then P_526 to MANUFACTURING_SUMMARY.
These System Defined Partition names use a *global* sequence, not tied to a specific table.

Can you rename the System Defined Partition after it has been automatically created ?

SQL> alter table manufacturing_summary
2 rename partition SYS_P519 to Y18M07
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P520 to Y18M09
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P521 to Y18M08
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P526 to Y18M10
3 /

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',
Y18M07 TO_DATE(' 2018-08-01 00:00:00',
Y18M08 TO_DATE(' 2018-09-01 00:00:00',
Y18M09 TO_DATE(' 2018-10-01 00:00:00',
Y18M10 TO_DATE(' 2018-11-01 00:00:00',

SQL>


Yes, fortunately, you CAN rename the Partitions *after* they are automatically created.



Categories: DBA Blogs

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet)

Richard Foote - Wed, 2018-10-24 20:15
In Part I and Part II of this series, we looked at how Global Indexes can effectively perform “Partition Pruning” when the partition keys are specified in SQL predicates, by only using those index entries that have a Data Object of interest stored within the index Rowids. In this piece, I’ll cover the key performance […]
Categories: DBA Blogs

Oracle Database EM 18 XE Available to Remote Clients

Surachart Opun - Mon, 2018-10-22 10:58
I found lot of posts about Oracle Database 18 XE. It's very interesting for me.  I didn't blog about how to install, because it's very easy for using rpm package and document very helpful.
I was interested in Enterprise Manager Database Express 18.4.0.0.0. How it looks like?
- Installing. I used CentOS7.
[student@centos-learning ~]$ sudo yum -y localinstall oracle-database*18c*
[student@centos-learning ~]$ sudo rpm -qa |grep oracle
oracle-database-preinstall-18c-1.0-1.el7.x86_64
oracle-database-xe-18c-1.0-1.x86_64

[student@centos-learning ~]$ sudo /etc/init.d/oracle-xe-18c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
The password you entered contains invalid characters. Enter password:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
*********
Enter SYSTEM user password:
********
Enter PDBADMIN User Password:
*********
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
31% complete
34% complete
38% complete
41% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.
Connect to Oracle Database using one of the connect strings:
     Pluggable database: centos-learning.surachartopun.com/XEPDB1
     Multitenant container database: centos-learning.surachartopun.com
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
[student@centos-learning ~]$ netstat -ltn |grep 5500
tcp        0      0 127.0.0.1:5500          0.0.0.0:*               LISTEN
- As I didn't want to connect 127.0.0.1, I changed binding - "Making Oracle Database EM Express Available to Remote Clients"
SQL> !netstat -ltn |grep 5500
tcp        0      0 127.0.0.1:5500          0.0.0.0:*               LISTEN
SQL> !lsnrctl status | grep HTTP
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
SQL>
SQL>
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
PL/SQL procedure successfully completed.
SQL> !netstat -ltn |grep 5500
tcp        0      0 0.0.0.0:5500            0.0.0.0:*               LISTEN
SQL> !lsnrctl status | grep HTTP
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=centos-learning.surachartopun.com)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
- Browsed it - https://IP:5500/em

However, I got some error like "Connection with database failed. Database instance might be down."

- Checked alert log file and fixed.
Error: Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
bash-4.2$ tail -f alert_XE.log
2018-10-22T22:06:32.890217+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:06:38.489011+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:10:32.402822+07:00
Resize operation completed for file# 3, old size 501760K, new size 512000K
2018-10-22T22:15:55.791490+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.
2018-10-22T22:18:02.248906+07:00
Global ports off in Root, do SetGlobalPortEnabled(TRUE) to enable.Setting the Global Port for EM Express to Manage a CDB and the PDBs. (It might not be the right solution, but I just wanted to see EM).
SQL> select dbms_xdb_config.getHttpsPort() from dual;
DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500
SQL> exec dbms_xdb_config.SetGlobalPortEnabled(TRUE)
PL/SQL procedure successfully completed.- Login again.

It worked fine for now.

Reference: Installation Guide for Linux x86-64
Categories: DBA Blogs

ORA-08103: object no longer exists

Tom Kyte - Sat, 2018-10-20 23:06
Hi Chris/Connor, Could you please have a look ate below scenario: Whenever we process orders in bulk lets say bunch of 1000 orders, out of which certain orders are failing in the MERGE statement with an error "ORA-12801: error signaled in paralle...
Categories: DBA Blogs

Key-preserved table concept in join view

Tom Kyte - Sat, 2018-10-20 23:06
I see a lot of examples where primary keys are missing or indexes are not unique. But I didn't see anything matching this example. There are 3 tables joined, and the 3rd table has a compound key that gets one value from the first table and one from...
Categories: DBA Blogs

How to extract XML data using extract function

Tom Kyte - Thu, 2018-10-18 16:06
inserted row by using below statemet :- <code>insert into xmlt values('<?xml version="1.0"?> <ROWSET> <ROW> <NAME>karthick</NAME> <SALARY>3400</SALARY> </ROW> <ROW> <NAME>c</NAME> <SALARY>1</SALARY> </ROW> <ROW> <NAME>mani</NAME> <SALARY>1</SAL...
Categories: DBA Blogs

Elaborate why 5 & same table used in below query

Tom Kyte - Thu, 2018-10-18 16:06
<code>select distinct * from t t1 where 5 >= ( select count ( distinct t2.sal ) from t t2 where t2.deptno = t1.deptno and t2.sal >= t1.sal );</code> I'll be grateful if you can explain. how the number work ,5, without var...
Categories: DBA Blogs

Find if a string is Upper, Lower or Mixed Case, numeric, Alpha Numeric etc

Tom Kyte - Wed, 2018-10-17 21:46
Dear Experts, I populated a table with few rows of strings that are Upper/ Lower/ Mixed case, alpha-numeric, numeric etc. 1. Now I would like to evaluate they type of string using a case statement. I tried using regexp_like, but it fails when ...
Categories: DBA Blogs

Deadlock issue came while using set based sql

Tom Kyte - Wed, 2018-10-17 21:46
Hi Tom, We are using set based sql in my process, In that we are creating so many GTT tables in a package. And we are executing this package concurrently in more than ten sessions, these sessions will create temporary tables with different name a...
Categories: DBA Blogs

Foreign Keys with default values

Tom Kyte - Wed, 2018-10-17 21:46
Hello. I'm designing a database in Oracle 12.2 in Toad Data Modeler. It would get lots of inserts. I'm using identity columns as PK (basically I create a sequence and use it as default value in the column, sequence.nextval). When I connect the...
Categories: DBA Blogs

Migration of 6i Forms to APEX

Tom Kyte - Wed, 2018-10-17 21:46
Hi Team, I am trying to migrate forms 6i to APEX, but problem that i pose here is that i cannot completely migrate all the functionalities of my forms to Apex even after trying to correct Metadata it does not migrate forms completely. So, my q...
Categories: DBA Blogs

Error while opening Database

Tom Kyte - Wed, 2018-10-17 03:26
HI,THERE I HAVE A SITUATION HERE,DUE TO POWER OUTAGE, DATABASE KEEP GIVING ERROR <code> <code> select name,open_mode from v$database; NAME OPEN_MODE --------- -----------------...
Categories: DBA Blogs

Automatic Memory Management or manual SGA & PGA setup?

Tom Kyte - Wed, 2018-10-17 03:26
Hi Tom, I need some clarification regarding the pros & cons of Oracle's AMM compare to us manually configuring SGA_TARGET & PGA_Aggregate. I read from quite a number of blogs & posts that doesn't recommend Oracle AMM on mission critical product...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs