DBA Blogs

DB Copy

Tom Kyte - Fri, 2018-11-02 03:46
Hi, Tom. I have two DB instance on different server. One DB is main, second db is "archive". We need copy only data from first db to second which contains CLOB and BLOB info, whole table. It's not copy all data from one to second. It's copy some da...
Categories: DBA Blogs

Manual dataguard

Tom Kyte - Fri, 2018-11-02 03:46
hi there we are using oracle database 11g (11.2.0.1.0 Standard Edition one) for our production db i have gone through the docs to setup automatic log apply which is available only for Enterprise Edition.. i saw the notes from the doc saying ...
Categories: DBA Blogs

How can I create a table with a parallel option

Tom Kyte - Fri, 2018-11-02 03:46
Hi Tom, I will create a table with 1000M records. Can I create the table with some level parallel option setting. Because later on, some people(end user) will be accessing the table with kinds of queries with different conditions, my purpose is to...
Categories: DBA Blogs

Performancetuning UTL_SMTP

Tom Kyte - Fri, 2018-11-02 03:46
Hi Tom, we are using UTL_SMTP so send a large amount of mail. It's a newsletter-project and the mails must be delivered in a short time-period. All Mails are relayed through a local linux-box mailserver (exim) with an 2Mbit Internet-Connection....
Categories: DBA Blogs

Can you see the values of bind variables without enabling trace?

Tom Kyte - Fri, 2018-11-02 03:46
Sometimes I want to capture the SQL a process is running, and often the quickest method is to use the session browser tool in Toad, SqlDeveloper, or just the v$session and v$sql tables directly. However what I obviously see is this: <code> Select...
Categories: DBA Blogs

How do i prevent end users from connecting to the database other than my application?

Tom Kyte - Fri, 2018-11-02 03:46
Hi Tom, Thanks for your help. We have a client/Server and a web interface. We want our end users to connect to the database only through our applications. How can we prevent the user from connecting to the database using SQL*PLUS or Microso...
Categories: DBA Blogs

Exchange partition fails with equal fields and different bytes due to cast ( timestamp )

Tom Kyte - Thu, 2018-11-01 09:26
<i></i>Hi, I'm testing a few Exchange Partition concepts to improve a process that I created. The database version I'm using is: <b><i>Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - ...
Categories: DBA Blogs

Get the first word in a string

Tom Kyte - Thu, 2018-11-01 09:26
Haai.. i have input like this in below.. <code>Create table Char ( name varchar2(1000) ); Insert all into Char(name) Values('Ask Tom') into Char(name) Values('Become Again Tom') into Char(name) Values('United Sates') select * from du...
Categories: DBA Blogs

Enforce either of column1 or column2 to be not null

Tom Kyte - Thu, 2018-11-01 09:26
Hi, I have a table where I need either of the column to be not null, meaning at any point of time only one of these column should have a value and the other should be null. I tried below: <code>CREATE TABLE "BCKC"."TEST" ( "ID" NUMBER(9,0),...
Categories: DBA Blogs

SET COMMAND

Tom Kyte - Thu, 2018-11-01 09:26
Sir I am getting the following error message after executing the command "SET DESCRIBE DEPTH 4 INDENT ON" at SQL prompt SET DESCRIBE DEPTH 4 INDENT ON" is Obsolete Please advise how to describe nested object types Regards DS Rao
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs