SQL & PL/SQL :: Access Method To Retrieve Single Row
Sep 15, 2010Which Oracle access method is the fastest way for Oracle to retrieve a single row?
a) Primary key access
b) Access via unique index
c)Table access by ROWID
d)Full table scan
ADVERTISEMENT
Application Express :: ORA-24247 / Network Access Denied By Access Control List
Apr 5, 2013When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.
I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.
ORA-24247: network access denied by access control list
<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI
<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;
[code]...
ORA-24247 / Network Access Denied By Access Control List (ACL)
Dec 12, 2012I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18
is it possible to send mails from oracle database in STANDARD Edition?
SQL & PL/SQL :: ORA-24247 / Network Access Denied By Access Control List (ACL)
Nov 15, 2011While Running the proc,it's raising the below error.
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1
[code]....
SQL & PL/SQL :: Fastest Method For One To One Update
May 9, 2013We have a requirement to update one column as given in the below mentioned pl/sql block.The challenge is to update the a large volume of data every day.Generally this kind of one to one update will takes long time (45 min approxmately) We need a solution that will be able to update 10 million records in few minutes.
begin
for i in (select t3.event_id, t1.header_id from
table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id)
loop
update table_1 set event_id = i.event_id
where header_id =i.header_id;
end loop;
commit;
end;
SQL & PL/SQL :: Novalidate Constraint Method
Mar 4, 2011NOVALIDATE CONSTRAINT METHOD.
Below is the step i gone through:
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below.
ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1:
ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
SQL & PL/SQL :: FIFO Method Calculation
Jun 11, 2010I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
Fast Method To Drop Many Users
Oct 21, 2010I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
Oracle 8i Best Method To Reduce DB Size?
Apr 3, 2008i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.
I've looked at shrink etc methods but some are not compatible with 8i.
I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.
How To Check Status Of Import (old Method) Job
Aug 23, 2011Question:
How to find out the status of the import job(how much work is done, ETA), i have started the job few days back it still running on the server. i am using import not datapump, because i got the dump which took with export. DB version is 11.2.0.2.0
for datapump i know i can use the following queries, but these are not giving any output l think these are only for datapump
select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;
SQL & PL/SQL :: Best Method To Populate Calendar Entries On A Table
Jun 2, 2011I have a table :
Product
A
B
C
D
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.
This would be done for all products for 4 years.
Windows :: Select Installation Method Does Not Display?
Feb 11, 2013I have installed oracle 10g in D drive .
The instalation says it is succeessful, without asking the database name or the password .
I have followed the steps given in the attached file .
However it is not asking the databse name or password ( no configuration set up is appearing for the user to interact with the system ) .
So, after installation I can not give the username and password .
The tnsnames.ora file is given below
# tnsnames.ora Network Configuration File: D:DevSuiteHome_1
etworkadmin nsnames.ora
# Generated by Oracle configuration tools.
[Code]....
The files from where I installed is ds_windowx86 disk1 and disk 2.
Forms :: Any Method On WHEN-BUTTON-PRESSED Trigger
May 9, 2012I have a form of a user feedback with SMS Sending. which I gave to my all clients when ever my any client enter a feedback they press send SMS button so I got SMS if they press the button 2 times I got 2 same sms and if press the button 3 times I got 3 same sms and so on I want to restrict them and allow them to send only 2 sms of that feedback.any method on WHEN-BUTTON-PRESSED Trigger.
View 7 Replies View RelatedSQL & PL/SQL :: Solve Equation With Newton-Raphson Method
Feb 25, 2010Is it possible to solve an equation with the newton-raphson method with sql?
I have an equation f(x)=1-a/x**2, f(x)'= 2a/x**3
and want to solve x for several a's
The newton-raphson methos uses the derivation as next approximation:
xn2 = xn1 - f(x)/f(x)'
The result should be like this:
create table temp_res (a integer, res number);
insert into temp_res values(1, 1);
insert into temp_res values(2, 1.41421288939285);
insert into temp_res values(3, 1.73205078513617);
insert into temp_res values(4, 1.99999999999617);
insert into temp_res values(5, 2.23606797658102);
insert into temp_res values(6, 2.4494897130809);
insert into temp_res values(7, 2.64575095945042);
insert into temp_res values(8, 2.82842712474403);
Does Oracle 11g Support Host Naming Method
Jun 30, 2013DB: 11.2.0.3 & OS: RHEL5 Easy Connect Naming method enhances the host naming method by allowing for a port and service specification. My question is does Oracle 11g support Host naming method?
View 4 Replies View RelatedSecurity :: Encryption Method (password Starts With 06)?
Aug 29, 2013Our Audit Company has given us a recommendation:"Old DB Link encrypted Passwords: The password of the Oracle databases links are encrypted using DES (password starts with 05). This encryption methord is known and users can decrypt the passwords using a simple SQL query. Please recreate the database links to use the new encryption method (password starts with 06)."What does it mean and how can we perform this recommendation?
View 2 Replies View RelatedSQL & PL/SQL :: Convert Rows To Columns Using Pivot Method
Mar 16, 2010I tried to convert rows to columns using the pivot method.But i am not able to do that.This is what i tried:
SQL> desc pt
Name Null? Type
----------------- -------- ------------
NAME VARCHAR2(10)
YEAR NUMBER(4)
VALUE NUMBER(4)
[code]....
As you can see from the above output i am not getting as expected.
Expected output:
YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
1993 3000 1340 8700
1991 1000 1500 1250
1992 2000 1200 2323
Best Method To Remove Old Data Running On Variety Of Systems
Oct 17, 2011Our application has been installed at customers in North America, Europe and South America for several years, in some cases, over 10 years. At least one of our customers has hundreds of gigabytes of data. We are considering options for cleaning out the old data.
The database runs on a variety of systems (Linux, Windows, Unix) and in several version (Oracle 9, 10, and 11). We need a solution that works in all environments.
Two of our main criteria for a successful solution are that:
-It maintains application data referential integrity. Our application makes little use of foreign key constraints, so the cleanup process will apply critical business rules to candidate data to determine if it can be deleted or not.
-The operation of the cleanup program does not impact use of the system in production.
For various reasons (license cost, installation issues) the partitioning option is not available to us.
Alternative 1: Flag records for cleanup
This requires adding a 1-character column to each table. That is a one-time operation done during implementation. The procedure applies the business rules and sets the flag according to whether a row is to be deleted or not. Rows marked for deletion can be checked, reset, exported, etc. Finally, a separate process deletes all marked rows.
Advantage of this is that the deletion process will use a full table scan to find the marked records. There is no index navigation, so hopefully less overhead. Disadvantage is that its updating application data which might affect user's perceived system response. There is some undefined concern that locking or other table activity involved with updating the flags could impact users.
Alternative 2: Build a list of keys for data to be deleted
We will build a list table during implementation. The first process examines the application data, applies the deletion rules and writes key information to the list for data that can be deleted. The list can be checked, reset, rebuilt and listed rows can be exported as required. Finally, the cleanup process uses the list to find and delete the data.
Advantage is that it doesn't update the application data as its building the list. Disadvantages are that it that there is some overhead in building and checking the list. The list requires more space than the flags in alternative 1 but we can handle that in various ways. The procedure needs to navigate key structures during the delete step as well as in the list-building phase.
Server Administration :: Index And Table Rebuild Method
Jan 17, 2012The best way to rebuild index and table and also give the reason as why we need to use this method?
View 10 Replies View RelatedServer Administration :: Method To Execute Schema Stats?
Mar 22, 2012When we want to gather schema statistics which method we should follow and why ?
exec dbms_utility.compile_schema('SHIKHAR');
or
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'JACK'
[Code].....
Server Utilities :: Oracle 11.1.0.7.0 - Export / Import Method?
Jul 31, 2011I am using Oracle 11.1.0.7.0 version and UNIX-Hp OS.
which export/import method should i use when i am to do export/import... like table level, tablespace level ,full database level, schema level?
1.Datapump or
2.Traditional export/ import ?
ODP.NET :: Wallet (file Method) Compatible With Managed Driver 12.1.0.1.0?
Oct 8, 2013I am trying to use oracle wallet with the new odp.net managed driver, but I am getting
"invalid username/password".
As the wallet itself is ok (it works with the unmanaged client), seems to me that this new provider is not fully compatible with Oracle Wallet. Is this true? I am trying this:
<oracle.manageddataaccess.client> <version number="*"> <settings> <setting name="tns_admin" value="D:oracleproduct11.2.0client_1
etworkadmin" /> <setting name="WALLET_LOCATION" value="D:oraclewallets" /> </settings> </version> </oracle.manageddataaccess.client>
SQL & PL/SQL :: ORA-22370 / Incorrect Usage Of Method AnyData Insert
Mar 26, 2010Any success using the sys.anydata.ConvertClob member function when trying to insert data into a Anydata column? This function has been listed in Oracle documentation since 9i, but even in 11G I get the error :
ORA-22370: incorrect usage of method AnyData Insert.
Code is pretty straight forward using a CLOB column in a after insert trigger :
Insert into TRACE_DETAIL(OBJ_ID, COLUMN_ID, OLD_VALUE, NEW_VALUE) values (logId, 73, null, sys.anydata.convertClob(:NEW.Req_Data));
Best Tool / Method - Move Data In Real Time?
Sep 22, 2010We have oracle 9i database named A with 2 schema A1 and A2.User enters data at A1 schema and the incremental data moved to A2 schema after some verfications performed by some scheduled job on A1 schema.
we want to move this incremental data to B1 schema of another oracle 9i database B at the real time when data gets entered to A2 schema of A oracle 9i database. We have access to A2 schema of A database and B1 schema of B database.way to do it or best practice to do this activity or if there is any third party tool or any available oracle utility to perform it?
Note: A2 schema of A database and B1 schema of B database has one to one mapping.We want to avoid using trigger on A2 schema, how data gets populated from A1 to A2 schema .
Server Utilities :: Total Estimation Using BLOCKS Method - Datapump
Apr 5, 2013I have analyzed that, datapump estimation is 9.902GB. When i check size of .dmp file, it's shows 1.44Gb.
Export: Release 11.2.0.1.0 - Production on Fri Apr 5 02:00:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_LVGITRN_30_24_050413.dmp directory=DP_DIR logfile=expdp_LVGITRN_30_24_050413.log full=y exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: [bold]9.902 GB [/bold]
Why Datapump estimate so much than actual size?
JDeveloper, Java & XML :: Pass Null As Argument In Any Method Parameter?
Feb 23, 2013why we pass null as a argument in any method parameter....What is its use in method reference..
View 1 Replies View RelatedPL/SQL :: How To Replace 2 Single Quotes To Single Quote
Nov 21, 2012I have an script.sql that receives as a parameter an string.
example:
@C:/myscript.sql "o'connor"
user_account_value varchar2(120) := '&1';
EXECUTE IMMEDIATE "Select * from Table where column = :1 " USING user_account_value I am not sure how to deal with string that contains single quotes.
If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.
so my question is what options do I have to deal with dynamic queries and single quotes.
I tried replacing replace(myParameter,'''',''''''); but not working well.
Windows :: Fast Method To Install Oracle Client In Multiple Machines?
May 25, 2010I need to install Oracle Data Provider for NET (and all depending Oracle client SW components) in multiple client machines.
Exist any other options (faster) without installing oracle client on each of the machines?
SQL & PL/SQL :: Efficient And Fast Method Required For Audit Trailing Tables In A Schema?
Nov 17, 2011efficient & fast method for Audit trailing tables in a Schema for any insert /update /deletes for a table. I do not want to use db triggers because of performance issues.
presently we have as system which does audit trail as below:
1. If a user changes a column value for a table ( update/insert/delete) then we call a db package and pass the parameters like table name,col name,user, operation (ins/update/delete),old value, new value,date modified,user modified etc
2. the called package will insert a record in the audit trail table with the parameters passed
3. The audit trail table is used for report generation .
Performance Tuning :: Correct Method To Determine Table Actual Size
Aug 9, 2012Which is the correct method to calculate actual data size in a table? becaue when I serach in google, I saw the below line.
"Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table."
Method 1:
actual space = num_rows*avg_row_len
Method 2:
actual space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)