SQL & PL/SQL :: DBMS_CRYPTO Hash A BLOB In A Union Over Dblink?

Dec 27, 2012

With this table in local and remote database via a DBLink

CREATE TABLE PICTURES (
ID NUMBER(12),
PICNAME VARCHAR2(20),
PICTURE BLOB
);

How come I can get the hash of the picture column fine like this:

SELECT dbms_crypto.hash@dbLink(PICTURE, 3)from SCHEMA1.PICTURES@dbLink;

but I can't do it in a union with the local database?

SELECT dbms_crypto.hash@dbLink(PICTURE, 3)from SCHEMA1.PICTURES@dbLink
UNION
SELECT dbms_crypto.hash(PICTURE, 3)FROM SCHEMA1.PICTURES;

gives "ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server"

View 1 Replies


ADVERTISEMENT

Security :: How To Decrypt DBMS_CRYPTO.HASH Encrypted Value

Mar 27, 2013

note we need to decrypt the data which is encrypted using dbms_crypto.hash algorithm. it is possible to decrypt dbms_crypt.hash

eg

SQL> select DBMS_CRYPTO.hash(utl_raw.cast_to_raw('Foo'), 3) FROM dual;
DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('FOO'),3)
--------------------------------------------------------------------------------
201A6B3053CC1422D2C3670B62616221D2290929

View 2 Replies View Related

PLS-00201 / Identifier DBMS_CRYPTO Must Be Declared

Oct 26, 2012

I inherited the management of an Oracle 10.2.0.3 database Standard Edition on a 32 bit Microsoft Windows Server 2003 R2 platform with some invalided objects related to SYSMAN schema.

I suppose that probably these invalid objects are derived from a bad **previous upgrading operation*(probably from 10.2.0.1 to 10.2.0.3).Furthermore I looked for a solution on the net and I found that this trouble could be issued dropping and recreating again the Database Control Repository using emca plus specific commands. [URL]On Windows Systems I followed these commands on my test database and the repository was successfully created and all the previous invalidated objects where corrected.

However there is still a view not functioning. I tried to compile it again after the creation of the new Database Control Repository with the command alter view <viewname> compile but it returns the following errors:

Progetto: sqldev.temp:/IdeConnections%23jhoray01XDB.jpr
Errore(7,3): PL/SQL: Statement ignored
Errore(7,19): PLS-00201: identifier 'DBMS_CRYPTO' must be declared

View 2 Replies View Related

SQL & PL/SQL :: Is It Better To Use UNION (or) UNION ALL With DISTINCT

Apr 22, 2010

Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.

Query1:

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept

Query2:

SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)

I mean to say query1 is better (or) query2 is better,which query improves the performance.

View 1 Replies View Related

Hash Value Generation

Aug 21, 2012

I have a question.

When and Where HASH VALUE for a given SQL statement get generated?

View 5 Replies View Related

SQL & PL/SQL :: How Hash Is Computed

May 22, 2013

Is it true that the SQL hash is computed on the SQL text only ?

View 4 Replies View Related

SQL & PL/SQL :: What Is Hash Join

Jul 12, 2012

what is Hash join?how it is different from inner join?what is the sign used for inner join?(eg: like the (+) sign used for outer join)?

View 1 Replies View Related

PL/SQL :: How To Use Hash Join

Jan 13, 2013

If i have a table T1 and a table T2. Table T1 is having 100 rows and table T2 is having 20 rows. When performing a Hash join ,which table should be used to make the hash table ,the larger one or the smaller one and why ?IF the data set is too small for considerations then please conser table T1 with 10 million of rows and table T2 with 1 million of rows.

View 11 Replies View Related

Performance Tuning :: Different SQL ID For Same HASH-VALUE?

Nov 2, 2012

I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.

how a same query can have different SQL_ID's but same HASH_VALUE?

Note: Statistics are not modified on the base tables of this query.

View 10 Replies View Related

SQL & PL/SQL :: Hash Partition Table

Jun 27, 2010

I am facing a problem in fetching / updating records from a customer details table having around 20 million records. The table contains around 30 fields with 'MOBILE_NO' as primary key. most of the queries are having 'mobile_no' in where clause .I am planning to hash partition that table using mobile_no column as there is no other column available which can be used for partition.

clarify whether creating hash partition on such key would increase performance of data extraction as I have read on net that hash partitioning is not effective for performance tuning.

View 5 Replies View Related

Hash Group By Improvement?

Aug 7, 2013

I see that one of my queries from an application time is spending most of its time in the hash group by. I'm running Oracle 11g with a quarter rack exadata appliance. Is there a better way to run or design this table? query:

SELECT COUNT(*)
FROM (
        SELECT "DDTMDAY", "MRKTNM", "BSMNM", "BSCNM", "CLNM", "CSCDNM", "BTSID", "SECTSEQID", "BNDID", "FAID", SUM("VATTCNT"), SUM("VMBLORGCNT"), SUM("VMBLTERCNT"), SUM("VSILENTRETRYCNT"), SUM("VCUSTBLKCNT"), SUM("VAXSFCNT"), SUM("VCEBLKCNT"), SUM("VWCDBLKCNT"), SUM("VT1BHLBLKCNT"), SUM("VPWRBLKCNT"), SUM("VNONBTSEQBLKCNT"), SUM("VSFULCALLCNT"), SUM("VDRPCALLCNT"),

[code]...

View 13 Replies View Related

Combining Hash Cluster And Indexes

Oct 28, 2010

A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.

Site admin - this site is a very useful resource.

View 5 Replies View Related

SQL & PL/SQL :: Hash Join And Nested Loops

Mar 16, 2011

What is the difference between Hash join and Nested Loops in pl / sql?

View 1 Replies View Related

SQL & PL/SQL :: What Is Hash Join And Sort Merge

Jun 12, 2012

I tried to search on google for "Hash Join" And "Sort Merge". But unfortunatly i am unable to understand that articles. "Hash join" And "Sort Merge".

View 3 Replies View Related

Single Table Hash Clusters

Mar 16, 2013

I created a single table hash cluster like this :

create tablespace mssm datafile 'c:appmssm01.dbf' size 100m
segment space management manual;

create cluster hash_cluster_4k
( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;

-- Created a table in cluster with row size such that only one record fits one block and inserted 5 records each with a distinct key value

CREATE TABLE hash_cluster_tab_8k
( id number(2) ,
txt1 char(2000),
txt2 char(2000),
txt3 char(2000)
)
CLUSTER hash_cluster_8k( id );
[code]....

If I issue the same query after creating unique index on hash_cluster_tab(id), the execution plan shows hash access and single I/O (cr = 1).Does it mean that to have single I/o in a single table hash cluster, we have to create unique index? Won't it create additional overhead of maintaining an index?

What is the second I/O needed for in case unique index is absent?

View 25 Replies View Related

Hash Partition On Primary Key Columns?

Jul 18, 2012

I have a existing non partition table with more than 100 million records,planning to re design using Hash partition.This table doesn't has any range column to do range partitioning.

Table has 40 columns with a Primary Key on two columns (guest_sales_Id ,Version Flag). guest_sales_Id is unique for entire table but with anopther column version Flag declared as Primary key.(Version Falg will have only two distinct values in entire table)

If i do hash partition,do i need to declare on two columns which are declared ad Primary key ?If i use only guest_sales_id to declare hash prtition any issues ?

View 2 Replies View Related

SQL & PL/SQL :: Hash Partition On Fact Table

Sep 27, 2010

I created the 32 hash partition on a fact table. Based on hash parititon technique it should evenly distribute data accross the different partition.But when i analyze the table and check the distribution its not at all even.

P_Name Tabspace num_Rows
SYS_P21TBS_TBS0
SYS_P22TBS_TBS0
SYS_P23TBS_TBS0

[Code]....

View 5 Replies View Related

Performance Tuning :: Find SQL Using Hash Value - ORA-00060

Nov 18, 2010

Getting an ORA-00060 in our database. This is the alert log:

Wed Nov 10 08:01:54 2010
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/admin/ngboot/bdump/ngboot1_lmd0_13119.trc.

This is part of the lmd file:

oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs

[Code] ......

How do I find the SQL causing this? I have the hash value of 3180952482. should I open up en SR as this i logged in an lmd trace?

View 2 Replies View Related

Server Administration :: How To Calculate Number Of Hash Keys

Mar 31, 2011

I was reading the documentation for oracle 11gr2, with reference to URL>.....

The following examples show how to correctly choose the cluster key and set the HASH IS, SIZE, and HASHKEYS parameters. For all examples, assume that the data block size is 2K and that on average, 1950 bytes of each block is available data space (block size minus overhead).Note that 34 hash keys are assigned for each data block

how they arrive at 34 hash keys because another portion of the document states

This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, then Oracle Database uses the next largest divisor.

if that is the case, then number of hash keys should be 1900/55 = 34.55 which should have rounded up to 35.

View 1 Replies View Related

Use Range-hash Partitioning Of A Large Dimension Table

Apr 12, 2013

At moment we use range-hash partitioning of a large dimension table (dimension model warehouse) table with 2 levels - range partitioned on columns only available at bottom level of hierarchy - date and issue_id.

Result is a partition with null value - assume would get a null partition in large fact table if was partitioned with reference to the large dimension.Large fact table similarly partitioned date range-hash local bitmap indexes

Suggested to use would get automatic partition-wise joins if used reference partitioningWould have thought would get that with range-hash on both dimension.

View 3 Replies View Related

Performance Tuning :: Create Hash Partition On Fact Tables?

Aug 5, 2010

I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.

View 10 Replies View Related

Server Administration :: Partition Exchange In Hash Partitioned Table

Mar 13, 2012

While trying partition exchange feature of Oracle with 2 hash partitioned tables, I come to know that I can't directly exchange partitions between 2 partitioned tables

I have two hash partitioned tables , so to move partition data from one table to another will include-

1) Exchange from partitioned table to non-partitioned table.
2) exchange from non-partitioned table to new partitioned table.

But I am not sure in which hash partition my data will go in new partitioned table (data need to be moved has single key value on basis of which tables are partitioned),

View 2 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related

PL/SQL :: ORA 02070 And DBLink?

Jul 20, 2012

i am using 11g and pl sqlk developper, here is the script snippet

MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O
USING (
SELECT T.GRANTEE, I.INSTANCE_NAME
FROM DBA_ROLE_PRIVS T, V$INSTANCE I
WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP

[code]...

An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?

View 3 Replies View Related

Getting Data Over SQL Server DBLink

Apr 17, 2012

I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.

Say the remote SQL Server table was created like:

CREATE TABLE [MITLOC] (
[MLCONO] [numeric](3, 0) NOT NULL,
[MLWHLO] [nchar](3) NOT NULL,
[MLITNO] [nchar](15) NOT NULL,
[MLWHSL] [nchar](10) NOT NULL,
[MLBANO] [nvarchar](20) NOT NULL,
[MLCAMU] [nvarchar](20) NOT NULL,
[MLFACI] [nchar](3) NOT NULL,
[MLWHLT] [nchar](2) NOT NULL,
[MLSTQT] [numeric](15, 6) NOT NULL)

The remote dblink is named after M3PREP.

Then if I run:

CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.

If I run:

CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.

If I run:

CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.

I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?

CODE#
# HS init parameters
#
HS_FDS_CONNECT_INFO = M3PREP
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = M3PREP.LOG

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

View 1 Replies View Related

Way To Reduce Dblink Timeout

Dec 28, 2011

is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.

View 2 Replies View Related

Nested Tables By DBLINK

Feb 1, 2011

There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.

I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.

The data should be refreshed every day, the data changes moderately, there are more or less 500 records.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any way to select the clob value from dblink.?

View 7 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related

SQL & PL/SQL :: How To Create Procedure By Using Dblink

Jun 26, 2011

iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB.
----------
BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.

Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved