Feed aggregator

SQL Plan Directives

Tom Kyte - 8 hours 30 min ago
Team, reading this article <u>https://blogs.oracle.com/oraclemagazine/on-learning-from-mistakes</u> and setting up an demo like this in my local database. In the below example pretend OWNER as "state" and OBJECT_TYPE as "city" (similar to the examp...
Categories: DBA Blogs

To arrive alphanumeric value from a string

Tom Kyte - 8 hours 30 min ago
Hi Tom, I have a problem to arrive unique alphanumeric value I have to arrive unique short name(5digit) from names and insert into the table. We have to check existing short names and arrive next heirarchy. Eg.The names are like below ...
Categories: DBA Blogs

List tablespaces and usage

Tom Kyte - 8 hours 30 min ago
how can List all the tablespaces in the ORCL database by displaying the available space, used space, status and type. Also permanent and temporary files related to those tablespaces in a separate query.
Categories: DBA Blogs

Inconsistent behavior with CROSS APPLY and OUTER APPLY

Tom Kyte - 8 hours 30 min ago
This question is also asked on http://stackoverflow.com/questions/42593148/inconsistent-behavior-with-cross-apply-and-outer-apply by a Graphql collaborator who is developing pagination for supporting Oracle db. have a schema in Oracle-12c similar...
Categories: DBA Blogs

PCF Heathwatch 1.4 just got a new UI

Pas Apicella - 12 hours 11 min ago
PCF Healthwatch is a service for monitoring and alerting on the current health, performance, and capacity of PCF to help operators understand the operational state of their PCF deployment

Finally got around to installing the new PCF Healthwatch 1.4 and the first thing which struck me was the UI main dashboard page. It's clear what I need to look at in seconds and the alerts on the right hand side also useful

Some screen shots below

papicella@papicella:~/pivotal/PCF/APJ/PEZ-HaaS/haas-99$ http http://healthwatch.run.haas-99.pez.pivotal.io/info
HTTP/1.1 200 OK
Content-Length: 45
Content-Type: application/json;charset=utf-8
Date: Thu, 13 Dec 2018 10:26:07 GMT
X-Vcap-Request-Id: ac309698-74a6-4e94-429a-bb5673c1c8f7

    "message": "PCF Healthwatch available"

More Information

Pivotal Cloud Foundry Healthwatch

Categories: Fusion Middleware

Understand Oracle Text at a glance

Yann Neuhaus - 16 hours 37 min ago

What is Oracle Text?

Oracle Text provides indexing, word and theme searching, and viewing capabilities for text in query applications and document classification applications.

Oracle text activation for a user

create user ORATXT identified by oratxt ;
grant ctxapp to ORATXT ;
grant execute on ctxsys.ctx_cls to ORATXT ;
grant execute on ctxsys.ctx_ddl to ORATXT ;
grant execute on ctxsys.ctx_doc to ORATXT ;
grant execute on ctxsys.ctx_output to ORATXT ;
grant execute on ctxsys.ctx_query to ORATXT ;
grant execute on ctxsys.ctx_report to ORATXT ;
grant execute on ctxsys.ctx_thes to ORATXT ;
grant execute on ctxsys.ctx_ulexer to ORATXT ;

Oracle Text configuration and usage

To design an Oracle Text application, first determine the type of queries you expect to run. This enables you to choose the most suitable index for the task. There are 4 use cases with Oracle Text:

  1. Document Collection Applications
    • The collection is typically static with no significant change in content after the initial indexing run. Documents can be of any size and of different formats, such as HTML, PDF, or Microsoft Word. These documents are stored in a document table. Searching is enabled by first indexing the document collection.
    • Queries usually consist of words or phrases. Application users can specify logical combinations of words and phrases using operators such as OR and AND. Other query operations can be used to improve the search results, such as stemming, proximity searching, and wildcarding.
    • An important factor for this type of application is retrieving documents relevant to a query while retrieving as few non-relevant documents as possible. The most relevant documents must be ranked high in the result list.
    • The queries for this type of application are best served with a CONTEXT index on your document table. To query this index, the application uses the SQL CONTAINS operator in the WHERE clause of a SELECT statement.
    • Example of searching
    • SQL> select score(1), doc_id, html_content from docs where contains(html_content, 'dbi', 1) > 0;
      ---------- ---------- -----------------------------------------------------------
      4 1 <HTML>dbi services provide various IT services</HTML>
      4 9 <HTML>You can become expert with dbi services</HTML>
      4 3 <HTML>The compaany dbi services is in Switzerland.</HTML>

  2. Catalog Information Applications
    • The stored catalog information consists of text information, such as book titles, and related structured information, such as price. The information is usually updated regularly to keep the online catalog up to date with the inventory.
    • Queries are usually a combination of a text component and a structured component. Results are almost always sorted by a structured component, such as date or price. Good response time is always an important factor with this type of query application.
    • Catalog applications are best served by a CTXCAT index. Query this index with the CATSEARCH operator in the WHERE clause of a SELECT statement.
    • Example of searching
    • SQL> select product, price from auction where catsearch(title, 'IT', 'order by price')> 0;
      ----------------------------------- ----------
      IT Advice 1 hour 499
      Course IT management 3999
      License IT monitoring 199
      IT desk 810

  3. Document Classification Applications
    • In a document classification application, an incoming stream or a set of documents is compared to a pre-defined set of rules. When a document matches one or more rules, the application performs some action. For example, assume there is an incoming stream of news articles. You can define a rule to represent the category of Finance. The rule is essentially one or more queries that select document about the subject of Finance. The rule might have the form ‘stocks or bonds or earnings’.
    • When a document arrives about a Wall Street earnings forecast and satisfies the rules for this category, the application takes an action, such as tagging the document as Finance or e-mailing one or more users.
    • To create a document classification application, create a table of rules and then create a CTXRULE index. To classify an incoming stream of text, use the MATCHES operator in the WHERE clause of a SELECT statement. See Figure 1-5 for the general flow of a classification application.
    • SQL> select category_id, category_name
      from categories
      where matches(blog_string, 'Dbi services add value to your IT infrastructure by providing experts in different technologies to cover all your needs.');
      ---------- -----------------------------------
      9 Expertise
      2 Advertisement
      6 IT Services

  4. XML Search Applications
    • An XML search application performs searches over XML documents. A regular document search usually searches across a set of documents to return documents that satisfy a text predicate; an XML search often uses the structure of the XML document to restrict the search.
      Typically, only that part of the document that satisfies the search is returned. For example, instead of finding all purchase orders that contain the word electric, the user might need only purchase orders in which the comment field contains electric.

In conclusion, there is various uses cases for which Oracle Text will help you with text indexation. Before implementing, verify which will best suit your need. Also, it may be interesting to compare with an external text indexer like Solr which is also able to index your database via a JDBC driver.

I hope it may help and please do not hesitate to contact us if you have any questions or require further information.

Cet article Understand Oracle Text at a glance est apparu en premier sur Blog dbi services.

Partitioning -- 12 : Data Dictionary Queries

Hemant K Chitale - Wed, 2018-12-12 19:47
Here's a compilation of some useful data dictionary queries on the implementation of Partitioning.

REM  List all Partitioned Tables in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, table_name, partitioning_type, subpartitioning_type, partition_count,
def_subpartition_count as default_subpart_count
from dba_part_tables
order by owner, table_name

REM List all Partitioned Indexes in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, index_name, table_name, partitioning_type, subpartitioning_type, partition_count,
def_subpartition_count as default_subpart_count
from dba_part_indexes
order by owner, index_name

REM List Partition Key Columns for all Partitioned Tables
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as Partition Key may consist of multiple columns
select owner, name, column_name
from dba_part_key_columns
where object_type = 'TABLE'
order by owner, name, column_position

REM List Partition Key Columns for Table SubPartitions
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as SubPartition Key may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'TABLE'
order by owner, name, column_position

REM List Partition Key Columns for Index SubPartitions
REM (or filter by OWNER or NAME (NAME is INDEX_NAME when object_type='INDEX'))
REM Need to order by column_position as SubPartition may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'INDEX'
order by owner, name, column_position

REM List all Table Partitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
REM Need to order by partition_position
select table_owner, table_name, partition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_partitions
order by table_owner, table_name, partition_position

REM List all Table SubPartitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
select table_owner, table_name, partition_name, subpartition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_subpartitions
order by table_owner, table_name, subpartition_position

REM List all Index Partitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
REM Need to order by partition_position
REM Note : For Table Names, you have to join back to dba_indexes
select index_owner, index_name, partition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_partitions
order by index_owner, index_name, partition_position

REM List all Index SubPartitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
select index_owner, index_name, partition_name, subpartition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_subpartitions
order by index_owner, index_name, subpartition_position

I have listed only a few columns from the data dictionary views of interest.  You may extract more information by referencing other columns or joining to other data dictionary views.

Categories: DBA Blogs

How Can You Protect Your Business From Cybercrime?

Chris Warticki - Wed, 2018-12-12 19:15
Cybercrime is Real.

By 2021, the estimated annual cost of worldwide cybercrime damages will be $6 trillion[1]. You’ve seen the headlines—the average cost of a data breach in 2016 was $4 million[2]—many companies never recover.

The U.S. Department of Homeland Security advises, “It is necessary for all organizations to establish a strong ongoing patch management process to ensure the proper preventative measures are taken against potential threats[3].”

Oracle continues to periodically receive reports of attempts to maliciously exploit vulnerabilities for which Oracle has already released fixes. In some instances, it has been reported that attackers have been successful because targeted customers had failed to apply available Oracle patches. Oracle therefore strongly recommends that customers remain on actively-supported versions and apply Critical Patch Update fixes without delay[4].

Support Matters.

Oracle Support is the best way to legally receive mission critical security updates and protection for your Oracle Software. Because Oracle creates and owns the source code, we can address vulnerabilities and emerging threats in the source code. As an Oracle Premier Support customer you have access reliable quarterly Critical Patch Updates to the source and security bulletins.

When your business is on the line, there is no substitute for trusted, secure, and comprehensive

See why Oracle recommends patching to fix software vulnerabilities at the source on Oracle Forbes BrandVoice and read more on the importance of software patching to avoid data breaches.

Don’t become a cybercrime statistic.

Quick Question for Oracle Set define off query

Tom Kyte - Wed, 2018-12-12 14:26
Hi All, I just want to know that if i turned off the feature i.e. "SET DEFINE OFF" from my oracle database, will there by any consequences / disadvantages with my data or database, as i really need to turn it off for some queries. Please advi...
Categories: DBA Blogs

how to correctly identify the correct ASCII values for the special characters.

Tom Kyte - Wed, 2018-12-12 14:26
Hi The column Text contains the apostrophe as special characters. Did a dump. you can see the results below. How do I correctly search for these special characters and then replace them with apostrophe ? You see the first row has the ASCII valu...
Categories: DBA Blogs

SQL Window function to skip and add rows dynamically

Tom Kyte - Wed, 2018-12-12 14:26
<code>create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number); --Positive scenario sample data insert into test1 values(1,'A',trunc(sysdate),0,5.5,2); insert into test1 values(1,'A',trunc(sysdate)+...
Categories: DBA Blogs


Tom Kyte - Wed, 2018-12-12 14:26
Can you give me a solution for converting CLOBS datatype to VARCHAR datatypes, all the documents I refer to talk about converting BLOBS to VARCHAR and when I try and apply the examples to CLOBS, get errors
Categories: DBA Blogs

What is object_type program

Tom Kyte - Wed, 2018-12-12 14:26
Tom, I was recently looking at distinct object_type in dba_objects and noticed that one object_type was PROGRAM. Where can I find more information on object_type PROGRAM? Thanks...
Categories: DBA Blogs

JDeveloper 10g With OAF Release 12.2.7 Extension Bundle For Enterprise Command Center Now Certified

Steven Chan - Wed, 2018-12-12 12:59

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface infrastructure.

The latest Oracle JDeveloper patch is now available, providing support for the Release 12.2.7 Oracle Enterprise Command Center (ECC) Framework:

Where is this update documented?

Instructions for installing this patch are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.7 users who have deployed Oracle Enterprise Command Center may wish to apply this patch.

What's new in this patch?

This patch enables support for Oracle Enterprise Command Center.

Related Articles

Categories: APPS Blogs

Understanding Partitioning in #Exasol

The Oracle Instructor - Wed, 2018-12-12 08:40

Exasol introduced Partitioning in version 6.1. This feature helps to improve the performance of statements accessing large tables. As an example, let’s take these two tables:

Say t2 is too large to fit in memory and may get partitioned therefore.

In contrast to distribution, partitioning should be done on columns that are used for filtering:


Now without taking distribution into account (on a one-node cluster), the table t2 looks like this:

Notice that partitioning changes the way the table is physically ordered on disk.

A statement like

SELECT * FROM t2 WHERE WhereCol=’A’;

would have to load only the red part of the table into memory. This may show benefits on a one-node cluster as well as on multi-node clusters. On a multi-node cluster, a large table like t2 is distributed across the active nodes. It can additionally be partitioned also. Should the two tables reside on a three-node cluster with distribution on the JoinCol columns and the table t2 partitioned on the WhereCol column, they look like this:

That way, each node has to load a smaller portion of the table into memory if statements are executed that filter on the WhereCol column while joins on the JoinCol column are still local joins.

EXA_(USER|ALL|DBA)_TABLES shows both the distribution key and the partition key if any.

Notice that Exasol will automatically create an appropriate number of partitions – you don’t have to specify that.

Categories: DBA Blogs

[BLOG] Know About Compute Classic in New Oracle Cloud Account as of Dec 2018

Online Apps DBA - Wed, 2018-12-12 05:34

Are you learning Oracle EBS R12 on Cloud and want to enhance your knowledge on the same? If yes, then visit: https://k21academy.com/ebscloud29 and learn about: ✔What is Compute Classic ✔The 2 types of Compute Classic ✔Steps to enable Compute Classic Services to your Oracle Cloud Account & much more… Are you learning Oracle EBS R12 […]

The post [BLOG] Know About Compute Classic in New Oracle Cloud Account as of Dec 2018 appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Historical Links for Oracle Net Services aka SQL*Net

Christopher Jones - Wed, 2018-12-12 00:06

Sometime you just have to do a cleanup but it seems a waste not to keep information that may still be valuable for the historical record. This is one such time. Here are some of the older resources about Oracle Net Services, aka SQL*Net taken from a web page that is about to be revamped.

Of course, if you want current information, visit the Oracle Net Service page, the Net Services Net Services Reference, or the latest Net Services Adminstrator's Guide.


Oracle Net Services provides a scalable, secure, and easy-to-use high-availability network infrastructure for Oracle environment. It eases the complexities of network configuration and management, maximizes performance, and improves network security and diagnostic capabilities as summarized hereafter.

  • Connectivity: Oracle Net Services enables a network session from a client application to an Oracle database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server. It is responsible for establishing and maintaining the connection between the client application and database server, as well as exchanging messages between them.
  • Manageability: it encompasses location transparency, centralized configuration and management, quick installation and configuration. Location Transparency service allows a database client to identify the target database server; to achieve this goal, several naming methods are available today: Oracle Net Directory naming, Local naming (TNSNAMES.ORA), Host naming, and External naming.
  • Centralized Configuration and Management: allows administrators, in large network environments, to easily access a centralized repository (i.e., LDAP-compliant directory server such as Oracle Internet Directory) to specify and modify the network configuration.
  • Quick Installation and Configuration: networking components for the Oracle database server and clients are preconfigured for most environments. The Oracle database service is resolved using various naming methods. As a result, clients and servers are ready to immediately connect when installed.
  • Performance and Scalability: features such as Database Resident Connection Pool (connection pooling), Shared Server (session multiplexing), and scalable event models (polling) enable performance and high scalability.
  • Network Security: Oracle Net Services enables database access control using features of firewall access control and protocol access control.
  • Diagnosability: a diagnostic and performance analysis tool, Trace Assistant, provides detailed information about the source and context of problems as they arise.
What's New in Oracle Database 12c

The new features for Net Services in Oracle Database 12c include:

  • Internet Protocol Version 6 (IPv6) Addressing and Connectivity support over Public VIPs to Real Application Clusters (please refer the IPv6 Statement of Direction below for more information)
  • Networking support for new architectures, such as, Database Consolidation and Global Data Services
  • Large Session Data Unit (SDU) sizes, with a new upper limit of 2 MB. The larger SDU size can be used to achieve better utilization of available bandwidth in networks that have high bandwidth delay products and host resources, according to application characteristics.
  • Advanced Network Compression. This feature can be used to reduce the amount of data transmitted over the network. New parameters for the sqlnet.ora file enable compression and selection of the preferred compression level. Please refer the Advanced Network Compression white paper below for more information.
  • Dead Connection Detection has been enhanced to reduce the amount of time taken to detect terminated connections. The SQLNET.EXPIRE_TIME parameter must be set in the sqlnet.ora file to detect terminated clients. If the system supports TCP keepalive tuning, then Oracle Net Services automatically uses the enhanced detection model, and tunes the TCP keepalive parameters at a per-connection level.
  • Intelligent Client Connection establishment feature reduces the priority of connection attempts to unresponsive nodes in the address string of connect descriptors. No configuration changes are required to use this feature.
  • Incident Generation for Process Failures for the following Oracle Net Services components has been added:
    • Oracle Net Listener
    • Listener control utility (LSNRCTL)
    • Connection Manager Administration (CMADMIN)
    • Oracle Connection Manager gateway (CMGW)
What's New in Oracle Database 11gR1 & 11gR2

The new features for Net Services in Oracle Database 11g Release 2 include:

  • Internet Protocol Version 6 (IPv6) Addressing and Connectivity for single-instance database (please refer the IPv6 Statement of Direction below for more information)
  • Oracle Restart in single instance environment for restarting the listener, the database and other Oracle components following hardware/software failure or host restart.
  • Support for CONNECT_TIMEOUT and TRANSPORT_CONNECT_TIMEOUT for each IP address
  • CIDR notation and wildcard support for valid node checking
  • The new features for Net Services in Oracle Database 11g Release 1 include:
    • Support for authenticated LDAP access for Net Naming
    • Performance Improvements
    • Fastpath for common usage scenarios, enforced transparently (no configuration required)
    • Efficient network support for bulk/large data transfers (such as, SecureFile LOBs)
    • Efficient event dispatch mechanisms for PHP and process-based systems (enforced transparently, no config. required)
    • Fast Reconnects for High Availability: efficient detection of terminated instances and conect time failover
    • Support for Database Resident Connection Pool
    • Enhancements to the Easy Connect Naming method

For more details see the Oracle Database Net Services Administrator's Guide

Technical Information

Oracle OpenWorld

Oracle Database 12c

Oracle Database 11gR2

Oracle Database 11gR1

Oracle Database 10g

Oracle Database 9i

Switch Insert to temp Segment Merge

Tom Kyte - Tue, 2018-12-11 20:06
Hi, some of our parallel direct path insert Statements suffer from enq: HV contention. These are big, straight forward inserts. Contention can be up to 60% of the db time. We did some tests and realized that temp Segment merge would get rid of t...
Categories: DBA Blogs

Oracle client 12.1 with the new OracleCredential class

Tom Kyte - Tue, 2018-12-11 20:06
Our SW includes component that uses ODP.NET, we upgraded ODP.NET to 18c to benefit from the new <i>OracleCredential</i> class. Problem: According to the Oracle.NET team, ODP.NET 18c would support only 18c clients, yet, we still need to support oth...
Categories: DBA Blogs

Most effective way to Delete large number of rows from an online table on a daily basis

Tom Kyte - Tue, 2018-12-11 20:06
I have a need to write a cleanup script that would delete old data (1-2 Million rows) based on a date on a daily basis. Almost equal amount of rows are inserted into the same table daily as well. Any suggestions on the most efficient way of doing th...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator