Feed aggregator

Creating dashboard with SQL Server Database

Nilesh Jethwa - Fri, 2017-04-14 11:40

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications?which may run either on the same computer or on another computer across a network (including the Internet).

InfoCaptor works with SQL Server using jTDS jdbc driver. http://jtds.sourceforge.net/ 
Some more information on connection issues with SQL server http://www.infocaptor.com/dashboard/jdbc-with-sqlserver-connection-refused-connect

Read more at http://www.infocaptor.com/ice-database-connect-dashboard-to-sqlserver-sql

Automate and expedite bulk loading into Windchill.

Data migration is the least attractive part of a PDM/PLM project.  Take a look at our latest infographic to learn how to speed up bulk loading data from Creo, Autodesk Inventor and AutoCAD, SolidWorks, Documents, WTParts and more into Windchill PDMLink and Pro/INTRALINK.

More information can also be found in our previous posts:

Approaches to Consider for Your Organization’s Windchill Consolidation Project

Consider Your Options for SolidWorks to Windchill Data Migrations

 

The post Automate and expedite bulk loading into Windchill. appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Touchpad tuning on linux: touchpad-indicator...

Dietrich Schroff - Fri, 2017-04-14 10:21
I encoutered some problems with my touchpad on ubuntu:
While typing some text, the touchpad signalled the mouse some strange moves. Sometimes it marked parts of the text and the next key erased the highlighted text...

First idea was to edit the xorg.conf or something else, but i found a less difficult was via touchpad-indicator.

It can be installed via
apt-get install touchpad-indicatorAlittle problem is, that you can not run it via cli with just calling touchpad-indicator, because ubuntu installs this program into /opt:
/opt/extras.ubuntu.com/touchpad-indicator/bin/touchpad-indicator 

Here you can choose "disable touchpad on typing" and you are done...

Character selectivity

Jonathan Lewis - Fri, 2017-04-14 06:40

A recent OTN posting asked how the optimizer dealt with “like” predicates for character types quoting the DDL and a query that I had published some time ago in a presentation I had done with Kyle Hailey. I thought that I had already given a detailed answer somewhere on my blog (or even in the presentation) but found that I couldn’t track down the necessary working, so here’s a repeat of the question and a full explanation of the working.

The query is very simple, and the optimizer’s arithmetic takes an “obvious” strategy in the arithmetic. Here’s the sample query, with the equiavalent query that we can use to do the calculation:


select * from t1 where alpha_06 like 'mm%';

select * from t1 where alpha_06 >= 'mm' and alpha_06 < 'mn';

Ignoring the possible pain of the EBCDIC character set and multi-byte national-language character sets with “strange” collation orders, it should be reasonably easy to see that ‘mn’ is the first string in alphabetical order that fails to match ‘mm%’. With that thought in mind we can apply the standard arithmetic for range-based predicates assuming, to stick with the easy example, that there are no histograms involved. For a range closed at one end and and open at the other the selectivity is:


( ( 'mn' - 'mm') / (high_value - low_value) ) + 1/num_distinct

The tricky bits, of course, are how you subtract ‘mm’ from ‘mn’ and how you use the values stored in the low_value and high_value columns of view user_tab_cols. So let’s generate the orginal data set and see where we go (running on 12c, and eliminating redundant bits from the original presentation):


rem
rem     Script:         selectivity_like_char.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2013
rem

execute dbms_random.seed(0)

create table t1 nologging as
with generator as (
        select rownum id
        from dual
        connect by rownum <= 1000
)
select
        cast(dbms_random.string('l',6) as char(6))      alpha_06
from
        generator,
        generator
where
        rownum <= 1e6 -- > comment to avoid WordPress formatting issue
;

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')

column low_value  format a32
column high_value format a32

select
        column_name,
        num_distinct,
        density,
        low_value,
        high_value
from
        user_tab_cols
where
        table_name = 'T1'
order by
        column_name
;

select min(alpha_06), max(alpha_06) from t1;

set autotrace traceonly explain

select
        *
from
        t1
where
        alpha_06 like 'mm%'
;

set autotrace off

It will probably take a couple of minutes to generate the data – it’s 1M random strings, lower-case, 6 characters fixed – and will take up about 12MB of space. Here are the results from the stats and min/max queries, with the execution plan for the query we are testing:


COLUMN_NAME          NUM_DISTINCT    DENSITY LOW_VALUE                  HIGH_VALUE
-------------------- ------------ ---------- -------------------------- --------------------------
ALPHA_06                  1000000    .000001 616161616E72               7A7A7A78747A


MIN(AL MAX(AL
------ ------
aaaanr zzzxtz


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   157 |  1099 |   265  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   157 |  1099 |   265  (20)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ALPHA_06" LIKE 'mm%')


Given that there are power(26,6) = 308,915,776 different combinations available for lower-case strings of 6 charactgers it’s not too surprising that Oracle generated 1M different strings, nor is it particularly surprising that the lowest value string started with ‘aaa’ and the highest with ‘zzz’.

So how do we get 157 as the cardinality for the query or, to put it another way, how do we get 0.000157 as the selectivity of the predicate. We need to refer to a note I wrote a few years ago to help us on our way (with a little caveat due to a change that appeared in 11.2.0.4) – what number would Oracle use to represent ‘mm’ and the other three strings we need to work with ?

According to the rules supplied (and adjusted in later versions) we have to:

  1. pad the strings with ASCII nulls (zeros) up to 15 bytes
  2. treat the results as a hexadecimal number and convert to decimal
  3. round off the last 21 decimal digits

We can model this in SQL with a statement like:


SQL> column dec_value format 999,999,999,999,999,999,999,999,999,999,999,999
SQL> select round(to_number(utl_raw.cast_to_raw(rpad('aaaanr',15,chr(0))),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),-21) dec_val from dual;

DEC_VAL
------------------------------------------------
505,627,904,294,763,000,000,000,000,000,000,000

1 row selected.

As an alternative, or possibly a cross-check, I created a table with a varchar2(6) column, inserted the four values I was interested in and created a histogram of 4 buckets on the column (there’s a suitable little demo at this URL) and got the following endpoint values:


ENDPOINT_NUMBER                                   ENDPOINT_VALUE
--------------- ------------------------------------------------
              1  505,627,904,294,763,000,000,000,000,000,000,000
              2  568,171,140,227,094,000,000,000,000,000,000,000
              3  568,191,422,636,698,000,000,000,000,000,000,000
              4  635,944,373,827,734,000,000,000,000,000,000,000

Once we’ve got these numbers we can slot them into the standard formula (not forgetting the 1/1,000,000 for the closed end of the predicate) – and to save typing I’m going to factor out 10^21 across the board in the division:

Selectivity = (568,191,422,636,698 – 568,171,140,227,094) / (635,944,373,827,734 – 505,627,904,294,763) + 1/1,000,000

Selectivity = 20,282,409,604 / 130,316,469,532,971 + 1/1,000,000

Selectivity = 0.00015564 + 0.000001 = 0.00015664

From which the cardinality = (selectivity * num_rows) = 156.64, which rounds up to 157. Q.E.D.


Plan Hash Values Changed For the Same SQL ID || How to determine why ?

Tom Kyte - Fri, 2017-04-14 06:06
Hello Team, I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all. I analysed what went wrong today and have detailed out what I could :...
Categories: DBA Blogs

the confusion about db_domain

Tom Kyte - Fri, 2017-04-14 06:06
Hi: Today I read the link below about the db_domain https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams052.htm#REFRN10036 it is said that Range of values of this parameter can not be null IS that right? because my datab...
Categories: DBA Blogs

RMAN - "report unrecoverable" command not listing the expected tablespace

Tom Kyte - Fri, 2017-04-14 06:06
I have added some rows to a table TAB_1 with NOLOGGING option after taking an RMAN backup. The tablespace TBS_1 is used for the table. When I tried to find the unrecoverable tablespaces using the command "REPORT UNRECOVERABLE", the expected tablespac...
Categories: DBA Blogs

Constraints

Tom Kyte - Fri, 2017-04-14 06:06
Is there a way I can drop a table without cascade if I have foreign keys pointing to the primary key of the table? Any idea?
Categories: DBA Blogs

CAST TABLE type not working with EXECUTE IMMEDIATE

Tom Kyte - Fri, 2017-04-14 06:06
Hi, I have created below TABLE type of VARCHAR2 - create or replace TYPE VC_ARRAY is table of varchar2(4000); and tables create table tb_test_src as select object_name, object_id from user_objects where rownum <= 20; create table tb_test...
Categories: DBA Blogs

UNUSABLE status of indexes , index partitions and index subpartitions

Tom Kyte - Fri, 2017-04-14 06:06
How can i find the unusable date of indexes, index partitions and index subpartitions , only with sqlplus , not looking in the alert log. - How can i find the date that the status of an index has became unusable ? - How can i know the date that t...
Categories: DBA Blogs

Data Encryption And Decryption In Tables

Tom Kyte - Fri, 2017-04-14 06:06
Team, As part of my request please consider the below create and insert statements : Version of DB being used is : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit create table bo_master(ssn number, bo_code number...
Categories: DBA Blogs

PLsql code to search and find pdf from folder and send in email as attachment

Tom Kyte - Fri, 2017-04-14 06:06
HI Oracle, i Have a requirement - search and find pdf from folder/directory and send via email to the particular receipient. here is more info about scenario: we have pdf files generated already in one folder/directory we need to send pdf f...
Categories: DBA Blogs

Merger the records with same Id

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, I have a below scenario.This is not fixed and it is dynamic in nature APPLICANT_NAME,Renewal_NAME,NUMBER,TYPE,SUBTYPE,ETHNICITY, GENDER,ZIP_CODE,QUESTION_id,ANSWER_Id,Answer JAN,LR-001352966,LPN.039005.MEDS-IV,Licensed Practical,,White,Fem...
Categories: DBA Blogs

sub queries

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, SQL> select * from customer_det; CID NAME DOP ---------- -------------------- --------- 1 Chandan 01-JAN-84 2 Ramu 01-MAR-84 3 Raghavendra 17...
Categories: DBA Blogs

Oracle E-Business Suite 12.2 Mobile and Web Services Security Requires Web Application Firewall (WAF)

This is the eighth posting in a blog series summarizing the new Oracle E-Business Suite 12.2 Mobile and web services functionality and recommendations for securing them.

Web Application Firewalls (WAFs) cannot replace the URL Firewall, nor can the URL Firewall replace WAFs.  The URL Firewall provides the critical function of only allowing those forms and web services that have been both hardened by Oracle and flagged by the client as being used – all other requests are blocked by the default-deny rules. The URL Firewall does not protect against common web attack techniques such as those below – this what WAFs protect against:

  • Denial of Service (DoS)
    • Flooding, recursive & oversized payloads
  • Injection & Malicious Code
    • XXC, SQLi, logic bombs, malformed content
  • Confidentiality and Integrigy
    • Parameter tampering, schema poisoning
  • Reconnaissance Attacks
    • Scanning and registry disclosure
  • Privilege Escalation Attacks
    • Race condition, format string, buffer overflow

Additional protection is required to secure Internet facing Oracle E-Business Suite web services. Third party WAFs can certainly be deployed, but Oracle Corporation’s API Gateway offers a compelling advantage for Oracle E-Business Suite clients. The API Gateway is a separate license option and is placed in front of the SOA Server (also a separate license option) to defend against the common web attack techniques specific to web services as identified above.

If you have any questions, please contact us at info@integrigy.com

-Michael Miller, CISSP-ISSMP, CCSP, CCSK

References
 
 
 
 
 
 
Web Services, DMZ/External, Oracle E-Business Suite
Categories: APPS Blogs, Security Blogs

Partner Webcast – Smarter Diagnostic and Insights for Enterprise IT Assets with Oracle ...

Across the globe, companieshave embraced cloud computing and the agility it provides. By constantly recombining technology andcloud solutions, enterprises are able to provide an ongoing stream of...

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

Partner Webcast – Smarter Diagnostic and Insights for Enterprise IT Assets with Oracle Management Cloud

Across the globe, companies have embraced cloud computing and the agility it provides. By constantly recombining technology and cloud solutions, enterprises are able to provide an ongoing stream of...

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

Deploying a SpringBoot Application to Oracle App Container - Part 1/2

In this post I am going to create a simple SpringBoot Application and deploy it to Oracle's Application Container Cloud Service. Oracle ACCS is a polyglot application hosting environment for a number...

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

Deploying a SpringBoot Application to Oracle App Container - Part 1/2

In this post I am going to create a simple SpringBoot Application and deploy it to Oracle's Application Container Cloud Service. Oracle ACCS is a polyglot application hosting environment for a number...

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

We're Moving! (This Blog)

PeopleSoft Technology Blog - Thu, 2017-04-13 18:15
This blog will be moving to a new platform on April 17th.  There may be a short period of time during the migration when the blog content is not available, but we'll be back up soon.  The new blog platform provides a more contemporary user experience, and it's responsive, so you will be able to read and interact with it easily on different form factors.   All the existing posts will be migrated and will be available on the new platform. You'll be able to find the blog in the same location.

Pages

Subscribe to Oracle FAQ aggregator