DBA Blogs

Oracle Database Configurations using Docker and Vagrant

Hemant K Chitale - Fri, 2018-08-17 02:16
Oracle now makes available configurations for the Database (and other products) on both Docker and Vagrant via GitHub.

Good time to familiarize oneself with GitHub, Docker and/or Vagrant.


For the Docker configuration see : https://github.com/oracle/docker-images/tree/master/OracleDatabase


For the Vagrant configuration see : https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase


(Note : The Examples have been available on GitHub for quite some time at https://github.com/oracle/oracle-db-examples )



Categories: DBA Blogs

Subtract hours and show milliseconds for a TIMESTAMP(6) column

Tom Kyte - Fri, 2018-08-17 02:06
I want to SELECT a TIMESTAMP(6) with milliseconds, but at the same time I need to subtract 3 hours (0.125 of a day) from that TIMESTAMP to convert it to my timezone. So I tried: <code>SELECT To_Char(UTCSCANTIME-0.125,'YYYY-MM-DD HH24:MI:SS') AS LO...
Categories: DBA Blogs

“Let’s Talk Database” coming to Wellington, Auckland, Brisbane and Perth in September.

Richard Foote - Thu, 2018-08-16 19:08
Due to popular demand, I’ve been asked by Oracle to again run some more “Let’s Talk Database” events in September. Dates and venues are as follows: Monday, 2 September – Wellington (Wellington Oracle Office): Registration Link. Tuesday, 3 September – Auckland (Auckland Oracle Office): Registration Link. Wednesday, 12 September – Brisbane (Brisbane Oracle Office): Registration Link. Thursday, 13 September – Perth (Perth Oracle Office): Registration Link. […]
Categories: DBA Blogs

Unable to gather table stats in parallel

Tom Kyte - Thu, 2018-08-16 07:46
Hi, We're running gather_table_stats with the following arguments: dbms_stats.gather_table_stats(ownname=>'&owner', tabname=>'&table',estimate_percent=>0.1,block_sample=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>fals...
Categories: DBA Blogs

Merge 2 PDF/png/jpg Files?

Tom Kyte - Thu, 2018-08-16 07:46
Good Evening Tom Is it possible to merge 2 binary files using oracle PL/SQL? I have seen examples using Java but not PL/SQL. Thanks for your time.
Categories: DBA Blogs

Scripts for Batch-Processing using the Data Dictionary in #Exasol

The Oracle Instructor - Mon, 2018-08-13 05:59

If you want to batch-process over a number of objects in Exasol, scripts that work with the Data Dictionary might do the trick. Let’s say I want to drop all of my tables that start with the letter P:

 

SQL_EXA> open schema fred;
EXA: open schema fred;

Rows affected: 0

SQL_EXA> create or replace script droptables
          as
          res=query([[select * from exa_user_tables where table_name like 'P%']])
          for i=1, #res
          do
           query([[drop table ::t]],{t=res[i].TABLE_NAME})
          end
        /
EXA: create or replace script droptables...

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.

Amazingly enough, DROP TABLE is an operation that can be rolled back in Exasol. So to safeguard, I can disable autocommit here.

SQL_EXA> set autocommit off;
SQL_EXA> execute script droptables;
EXA: execute script droptables;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------

0 rows in resultset.

SQL_EXA> rollback;
EXA: rollback;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
---------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.
Categories: DBA Blogs

Partitioning -- 3a : Indexes on a Partitioned Table

Hemant K Chitale - Sun, 2018-08-12 06:35
Building on the case study of the Range Partitioned Table from the previous Blog Post, here are some Indexes.

SQL> select index_name, tablespace_name, partitioned, uniqueness
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME TABLESPACE_NAME PAR UNIQUENES
------------------------------ ------------------------------ --- ---------
SYS_C0017514 HEMANT NO UNIQUE

SQL>
SQL> select column_name, column_position
2 from user_ind_columns
3 where index_name = 'SYS_C0017514'
4 /

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

SQL>


We have an Index automatically built for the Primary Key constraint (note that the Index was created in the user's DEFAULT Tablespace).  This Index is a Global, Non-Partitioned Index.   Therefore, any Partition Maintenance operation (for a non-empty Partition) on the table may set the Index UNUSABLE unless the UPDATE INDEXES clause is used.

SQL> select status from user_indexes
2 where index_name = 'SYS_C0017514'
3 /

STATUS
--------
VALID

SQL> alter table sales_data drop partition P_2016;

Table altered.

SQL> select status from user_indexes
2 where index_name = 'SYS_C0017514'
3 /

STATUS
--------
VALID

SQL> insert into sales_data
2 values (1001,to_date('01-FEB-2017','DD-MON-RR'),'ABC1001',1,10,1000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table sales_data drop partition P_2017;

Table altered.

SQL> select status from user_indexes
2 where index_name = 'SYS_C0017514'
3 /

STATUS
--------
UNUSABLE

SQL>
SQL> alter index SYS_C0017514 rebuild;

Index altered.

SQL> select status from user_indexes
2 where index_name = 'SYS_C0017514'
3 /

STATUS
--------
VALID

SQL>


When I dropped the *empty* P_2016 Partition, the Index remained valid.  However, when I dropped the *non-empty*  P_2017 Partition (even if it has a single row and irrespective of whether Table/Partition statistics have been gathered) without the UPDATE INDEXES clause, the Index became UNUSABLE.  An UNUSABLE Unique Index will not allow fresh inserts (into *any* Partition of the table).

Next, I attempt to create a Local Partitioned Index.  Such an Index has a Partition corresponding to each Table Partition.

SQL> create index sales_data_lcl_ndx_1
2 on sales_data (sale_date, invoice_number) local
3 /

Index created.

SQL> select partitioned
2 from user_indexes
3 where index_name = 'SALES_DATA_LCL_NDX_1'
4 /

PAR
---
YES

SQL> select partitioned, tablespace_name, status
2 from user_indexes
3 where index_name = 'SALES_DATA_LCL_NDX_1'
4 /

PAR TABLESPACE_NAME STATUS
--- ------------------------------ --------
YES N/A

SQL>
SQL> select partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name = 'SALES_DATA_LCL_NDX_1'
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 USERS USABLE

SQL>


A Local Partitioned Index is created with the LOCAL keyword in the CREATE INDEX statement.
For a Partitioned Index, the TABLESPACE_NAME and STATUS attributes carry no meaning at the Index level --- these have values for each Partition.

Note how the Index Partitions were created with the same Partition Name and Tablespace Name as the Table Partitions. Similarly, any Partition Maintenance operations (DROP, MERGE, SPLIT) at the Table level will automatically be applied to the Index, dropping/creating the corresponding Index Partition(s).

In my next post, I will look at two other types of Index definitions on a Partitioned Table  (Global Partitioned Index and Bitmap Index).




Categories: DBA Blogs

OPS$Oracle user after Wallet Creation in Oracle 12c

Pakistan's First Oracle Blog - Fri, 2018-08-10 00:37

----- In Oracle 12.1.0.2, created the wallet by using below commands:

TEST$ orapki wallet create -wallet "/u01/app/oracle/admin/TEST/wallet" -pwd ****  -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.


TEST$ mkstore -wrl "/u01/app/oracle/admin/TEST/wallet" -createCredential TEST2 sys ********
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1

----- But when I logged into the database with sys user, the show user showed OPS$ORACLE user instead of sys:

TEST$ sqlplus /@TEST2

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 9 13:09:38 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Aug 09 2018 03:18:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> sho user
USER is "OPS$ORACLE"
SQL>

----- So made following changes and it worked fine:

Put following entry in sqlnet.ora file:

SQLNET.WALLET_OVERRIDE = TRUE
The SQLNET.WALLET_OVERRIDE entry allows this method to override any existing OS authentication configuration.

and used mkstore to create the wallet:

TEST$  mkstore -wrl "/u01/app/oracle/admin/TEST/wallet" -createCredential TEST2 sys

Categories: DBA Blogs

SQL JSON ORA-40459 Exception

Tom Kyte - Thu, 2018-08-09 10:46
I am trying to use JSON functions like JSON_OBJECT and JSON_ARRAYAGG to generate a JSON string through SQL. We have alot of columns in our tables that have alot more data than 4000 bytes which I am trying to parse through and generate JSON string. Ev...
Categories: DBA Blogs

Merge Two Rows Into One Row With 2 Columns

Tom Kyte - Thu, 2018-08-09 10:46
Is there a way to simply merge via a single pivot or analytical query the results from two rows into one? We have a legacy table that is essentially storing key value pairs in columnar format. As an test case, here is an example: <code>create tabl...
Categories: DBA Blogs

Partitioning -- 2 : Simple Range Partitioning -- by DATE

Hemant K Chitale - Thu, 2018-08-09 03:35
Range Partitioning allows you to separate a logical table into a number of distinct physical segments, each segment holding data that maps to a range of values.
(I encourage you to read the Introduction in the first post in this series)

The simplest and most common implementation is Range Partitioning by a DATE column.

SQL> l
1 create table sales_data
2 (sale_id number primary key,
3 sale_date date,
4 invoice_number varchar2(21),
5 customer_id number,
6 product_id number,
7 sale_value number)
8 partition by range (sale_date)
9 (partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY'))
10 tablespace TBS_YEAR_2015,
11 partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY'))
12 tablespace TBS_YEAR_2016,
13 partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY'))
14 tablespace TBS_YEAR_2017,
15 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
16 tablespace TBS_YEAR_2018,
17 partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
18 tablespace TBS_YEAR_2019,
19 partition P_MAXVALUE values less than (MAXVALUE)
20 tablespace USERS
21* )
SQL> /

Table created.

SQL>


Here, I have created each Partition in a separate tablespace.  Note that the Partition Key (SALE_DATE) does not have to be the same as the Primary Key (SALE_ID)

I have also created a MAXVALUE Partition  (Some DBAs/Developers may mistakenly assume this to be a *default* partition.  Range Partitioning, unlike List Partitioning, does not have the concept of a "default" partition.  This simply is the Partition for incoming rows that have Partition Key value that is higher than the last (highest) defined Partition Key Upper Bound (31-Dec-2019 23:59:59 in this case)).

I can look up the data dictionary for these partitions in this manner :

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

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2015 TBS_YEAR_2015
P_2016 TBS_YEAR_2016
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_MAXVALUE USERS

6 rows selected.

SQL>


Partitions are ordered by Partition *Position*  not Name.

How do I add a new partition for data for the year 2020 ?  By "splitting" the MAXVALUE partition.

SQL> alter table sales_data                 
2 split partition P_MAXVALUE
3 at (to_date('01-JAN-2021','DD-MON-YYYY'))
4 into
5 (partition P_2020 tablespace TBS_YEAR_2020, partition P_MAXVALUE)
6 /

Table altered.

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

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------
P_2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD
P_2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD
P_2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD
P_2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
P_2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
P_2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD
P_MAXVALUE MAXVALUE

7 rows selected.

SQL>
SQL> l
1 select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4* order by partition_position
SQL> /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2015 TBS_YEAR_2015
P_2016 TBS_YEAR_2016
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

7 rows selected.

SQL>


Note that, irrespective of the data format I specify in the CREATE or SPLIT commands, Oracle presents the Upper Bound Date (HIGH_VALUE) in it's own format, using a Gregorian Calendar.

How do I remove an older partition ?

SQL> alter table sales_data
2 drop partition P_2015
3 /

Table altered.

SQL>


A DROP command is very simple.

In my next post, I will add Indexes to this table.



Categories: DBA Blogs

Partitioning -- 1 : Introduction

Hemant K Chitale - Thu, 2018-08-09 02:54
I am beginning a new series of Blog Posts on Partitioning in Oracle.  I plan to cover 11g and 12c.   I might add posts on changes in 18c  (which is really 12.2.0.2 currently)

First, this is my presentation at AIOUG Sangam 11
and this the corresponding article

This series of posts will have new examples, from the simple to the complex, not present in the above presentation / article.
Categories: DBA Blogs

Design question around automatic data change notifications

Tom Kyte - Wed, 2018-08-08 16:26
Hello Oracle experts. Thank you in advance for going through my question. In our organisation, we are planning to implement a solution to automatically push the data that is changed in our databases to consumers. They are not a high traffic system...
Categories: DBA Blogs

PLS-00172: string literal too long

Tom Kyte - Tue, 2018-08-07 22:06
Hi Tom, When i try to insert huge data into a CLOB colum, i get "PLS-00172: string literal too long". Tried searching in web/metalink, but of no use. Please let me know if a workaround is available for this insert. drop table t9; create tabl...
Categories: DBA Blogs

Time periods determined from input user dates and table defined periods

Tom Kyte - Tue, 2018-08-07 22:06
Hi, I have a procedure that takes 2 parameters (start and end date) and using some additional data from a "settings" table and some sales transactions, populates an intermediary table. The settings table was initially as 1 interval / 1 set of d...
Categories: DBA Blogs

Query a collection of JSON data

Tom Kyte - Tue, 2018-08-07 22:06
I need to query a collection of json data for specific data values contained. In this example, I'm trying to count the number of json objects where the LNAME attribute = 'Mallard'. Based on the documentation at: https://docs.oracle.com/en/d...
Categories: DBA Blogs

EMEA OCI Partner Deep Dive Days 10 – 12 September 2018 | London, UK

Dear Valued Oracle Partner,  Join us at our 1st annual EMEA Oracle Cloud Infrastructure (OCI) Partner Deep Dive Days event to learn the latest in OCI technical features as well as how to...

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

Syntax checking without execution of query

Tom Kyte - Tue, 2018-08-07 03:46
Hi Tom, I have a front end screen where user can form a select statement.Once user will submit the syntax checking I should verify whether the query syntatically correct or not?.How should I achieve above by using SQL/PLSQL?. For ex, ...
Categories: DBA Blogs

Setup Oracle database using Docker container

Learn oracle 12c database management - Mon, 2018-08-06 13:22
Step-1:
Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic 
if you are on windows 10 or higher use : Docker Community Edition (Download)

Docker Instructions : https://docs.docker.com/install/
Once installed you will see Docker Quickstart Terminal and Kitematic.


open docker machine and then Kitematic.

Search for oracle XE 11g by Seth89. Download and install the below shown container


Once installed You will have oracle database installed and ready to use. you can ignore the below error message  /docker-entrypoint-init.d/cache: no such file or dir.

Another way to confirm successful installation is you will see Unauthorized image in web preview section


Make sure you have bridge network configured. As below

Check if below configured ports are configured.

How to connect to oracle database in the container :
Based on the IP Address and port configured in above image. Use those credentials to connect to DB.
Username : System
Password : oracle


Or to connect via sqlplus use as below:



Optional settings :

To configure or change volumes use. Volumes will help you to copy files over or attach a local working directory to container:


Categories: DBA Blogs

Log buffer size tuning

Tom Kyte - Mon, 2018-08-06 09:26
Oracle documents say Log buffer is normally small compared with the total SGA size, its key ratio is the space request ratio: redo log space requests / redo entries. Could you tell me what is the key ratio we are looking when adjust log buffer in ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs