DDL Statements Fail Or Not When DML Active?

Dec 9, 2011

DDL statements automatically end with COMMIT the user transactions in which they appear. Foe example:

-------------------------------------------
create table mytable01 (i integer);
insert into mytable01
select 1 from dual;
create table mytable02 (i integer);
-------------------------------------------

After all three statements are executed, data are committed in mytable01.

In the Oracle DB server SQL guide we read:

"DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK."

But I executed the following without any problem:

-------------------------------------------
create table mytable (i integer);
insert into mytable
select 1 from dual;
commit;
update mytable
set i = 2;
alter table mytable add (j integer);
-------------------------------------------

So where's the truth? Are DDL statements blocked when they refer to an active object accessed from a DML or not?

View 5 Replies


ADVERTISEMENT

RAC & Failsafe :: Two Nodes Active / Active Configuration

Oct 15, 2013

I am getting starting with rac environments. I need to configure a two nodes active/active with rac and I need to know if always using rac both nodes will be active or if I have to do that at some point when installing. I have reading some docs from oracle and others authors about rac and its configuration process but that is not understood for my yet.

View 5 Replies View Related

Backup & Recovery :: RMAN Backup Strategy For Active-active Deployments Using Streams

Jul 25, 2012

We are using oracle as database in our application. For high availability we have a cluster of multiple nodes and the data is replicated using oracle streams. All the nodes in the cluster are active. We do not have any concept of stand-by.

Now we are planning to use oracle RMAN for backup and recovery. RMAN user's guide doesn't recommend any strategies for such deployments. It mainly focuses on primary/stand-by deployments.

View 1 Replies View Related

SQL & PL/SQL :: Fail While Validating Email ID

Jul 3, 2012

I am creating function for validating email id :

create or replace function f_is_valid_email(p_email in varchar2) return varchar2 is

begin
if REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+.{1}[a-zA-Z]$') then
return 'success';
ELSE
RETURN 'Failure' ;
END IF;
end f_is_valid_email;

But this function fail when

i did
SELECT f_is_valid_email('aaa..ss@AA.com') from dual
or
SELECT f_is_valid_email('aaa.ss@AA..com') from dual

View 8 Replies View Related

Server Utilities :: EXP / IMP Getting Fail?

Oct 23, 2011

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:UsersAdministrator>d:
D:>imp
Import: Release 10.2.0.1.0 - Production on Mon Oct 24 08:45:48 2011

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

[code]...

IMP-00058: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
IMP-00000: Import terminated unsuccessfully

View 10 Replies View Related

SQL & PL/SQL :: Insert Statements / Give A Commit One By One After Each Insert Statements?

Oct 11, 2012

Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?

View 8 Replies View Related

Clusterware :: Fail Safe Virtual IP?

Jul 3, 2012

I have a Windows 2008 R2 SP1 2 node active/passive cluster with fail safe 3.4.2 installed. I also have 11G database software installed. My problem is I am unable to successsfully add an oracle virtual IP to the oracle group within fail safe. I receive the below error when I try. I need to get this resolved so that I can add my databases to the cluster.

Oracle Fail Safe Manager

FS-10890: Oracle Services for MSCS failed during the create operation
FS-11220: Failed to create the virtual address
FS-10012: Failed to bring the cluster resource Network Name HHVHAVELP01 online. Check the Windows event log for messages.
FS-10726: Resource Network Name HHVHAVELP01 is in a failed state

I don't see any message in the event log when I check...

View 2 Replies View Related

Networking And Gateways :: Connect Time Fail Over?

Mar 12, 2010

I would like to implement connect time fail over from my JDBC thin client. The client will try to first connect with dedicated connection mode, if the connection time crosses more than two seconds, then connection will be opened with shared mode. To implement this, I have put the following connection string in my JDBC URL. I am able to connect to database with this URL, however it seems fail over does not work. Is it because, my database listener is not configured with dynamic service registration. If I implement the dynamic service registration, is fail over going to work? Note, I want to fail over in the same database instance but with different listener.

(DESCRIPTION_LIST =
(FAILOVER = TRUE)
(LOAD_BALANCE = FALSE)

[Code].....

View 11 Replies View Related

Forms :: WebUtil.cfg DLLs Fail To Download

Jul 14, 2011

I have set up a number of other developers on the team, using the same configuration setup which performs without error. We have the ORCALE Forms on our local PC, and have been testing them successfully.

We are using Forms Version 10.1.2.0.2, Oracle Database 10g Release 10.2.0.4.0, OS is Windows XP Pro SP3

We are currently using JInitiator 1.3.1.22, but will be using the Java plug in in a later release of our code. I have been making code modifications and testing for a number of months on the PC. To log into our application the dlls must first be downloaded onto the client PC from the web server. Tuesday morning a problem occured. I had modified the webutil.properties to get the DLLs to download again in assisting another developer in researching a problem he was having. Now the three files jacob.dll, d2kwut60.dll, JNIsharedstubs.dll fail to download at all, causing an error almost from the start in the application. The download dialog box does not display for these nor are they found then in the webutil.properties or JInitiator directory.

The user DLL's dds_lFileUtil.dll, dds_lcac.dll, DDS_lExtProc.dll download successfully, but the webutil.properties fails to update for the third dll, causing the missing dll to continue to download upon each time the application starts. This is evident from the download dialog box being visible, and the dll being found in the JInitiator directory.

I have read that you should be able to copy the DLL to the correct directory (JInitiator directory) and manually update the webutil.properties and then the functionality should work. However this is not true for us, the functionality still fails to log in successfully.

I included the WebUtil file below with commented lines removed for ease of reading. The formsweb.cfg is pointing to the env which points to the WebUtil.cfg correctly.

We are administrators on our PC's.

The questions:
1) Why are jacob.dll, d2kwut60.dll, JNIsharedstubs.dll failing to download?
2) Why is the third file not being written to the webutil.properties?

# ----------------------------------------------------------
# webutil.cfg - WebUtil configuration file
# ----------------------------------------------------------
logging.file=C:Forms_10glog_webutil.log
logging.enabled=TRUE
logging.errorsonly=TRUE
logging.connections=TRUE

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
install.syslib.location=/webutil
install.syslib.0.7.1=jacob.dll|94208|1.0|true
install.syslib.0.9.1=d2kwut60.dll|192512|1.0|true
install.syslib.0.9.2=JNIsharedstubs.dll|65582|2.0|true
[code].....

View 3 Replies View Related

Bring Down The Databases In Oracle Fail Safe Environment?

Jun 28, 2013

How can we bring down the databases in oracle fail safe environment?

We have one database X in two server�s windows A & B with oracle fail safe environment.
What procedure should we fallow to bring down the database X.

Today I was strangling to bring down the database because database was automatically coming up once brought down the database. what procedure should we follow to bring down the database in OFS environment.

View 1 Replies View Related

SQL & PL/SQL :: Update Query Fail With Error - Missing Right Parenthesis

Feb 17, 2010

my update query seems to fail with error

ORA-00907: missing right parenthesis

Below is my test case:

create table t1(
sn varchar2(30),
inv_org number);
create table s1(
sn varchar2(30),
trx_date date,
inv_org number);

[code]...

View 8 Replies View Related

RAC/ASM Clusterware Installation :: Oracle RAC Private Connection Fail

Feb 7, 2013

I would like to know that when the private network in oracle rac down, which is the preferable node in the cluster (cluster of 2 nodes) to takeover the cluster.

Can I set/get the failover policy to point to one node in the cluster?

View 8 Replies View Related

Enterprise Manager :: Client V9.2.0 Fail When Access Oracle Database 8.1.7?

May 12, 2010

behaviour of oracle enterprise manager. i want to edit a table of a database. after i clicked on the table the oem crashes without any messages - simply disappearing.

i installed oracle9i release2 V9.2.0.1.0 windows client and want to access an oracle8i V8.1.7 server on linux.

View 4 Replies View Related

Networking And Gateways :: Connection Fail Two Database With Identical Sid Name On Different Hosts

Feb 18, 2013

My production database server is cloned for test environnement . I kept the same name of SID for both environnement but on different hosts.

I got error when try to connect to test database : TNS:protocol adapter error.

Below the the TNSNAMES.ORA files:

----- production host ---

HOFL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asus-sol)(PORT = 1521))

[Code].....

View 10 Replies View Related

Data Guard :: Right Steps To Perform Failover In Case Primary Database Gets Fail

Jan 10, 2011

i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.

View 4 Replies View Related

Installation :: Fail To Start OCR And OS / Error When Start OracleCSService

Jun 5, 2012

I'm trying to install Oracle 10g Enterprise Ed. (on Windows 2003 Std. Ed) and I get some errors:

First “Fail to start OCR” I press "continue" and the installation continue. Next “Operative System Error when start OracleCSService” and when I press "continue" the installation finish.

At the end of installation, It seems like Oracle DB is Ok, but not really...I can't connect to database from Enterprise Manager and the OracleCSService is in "Starting mode".

View 3 Replies View Related

Import CSV Using SQL Statements

Jun 9, 2011

I know, importing a *.CSV-table is easy using a few clicks with the GUI, but I want to know, how to import and create new tables, using the existing *. CSV-files, with SQL statements.

how to import a .CSV-file using SQL-statements?

View 7 Replies View Related

SQL & PL/SQL :: IF / THEN Statements With Different Datatypes

Sep 30, 2011

I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)

1. divide the numerical data in the column by 10 and leave the char data alone (just return it)

2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data

I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:

select
case when '1234' = 'checked' then 'checked'
when '1234' = 'gen.nograde' then 'gen.nograde'
when '1234' = null then null
else '1234'/10 end as "GRADE"
from dual

Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"

View 4 Replies View Related

SQL & PL/SQL :: Getting Insert Statements

Feb 26, 2010

I have a read access on all the 200+ tables in a schema, How can i generate 200+ .sql files with insert statements on all the tables of schema.

View 13 Replies View Related

Active Directory Integration With EBS

Jun 3, 2013

I have task to integrate AD users with oracle ebs r12 . Is there any mechanism to do it directly without OID between oracle ebs and Active directory during login to ebs or i have to install OID to finish that task .

View 1 Replies View Related

Database Link Not Active

May 15, 2013

I am newbie to oracle database.

I am using oracle 9i version and in EMCA satndalone application . I have created database links.

The database links have been created, and three databases are local.

When i am pressing the test button in emca . It gives notification database link not active.

What should i do?

View 3 Replies View Related

Authenticate 10g Through Active Directory?

Aug 19, 2009

I am trying to authenticate Oracle 10g through Active Directory ..But i am not able to do that successfully...

View 2 Replies View Related

Active Dataguard Database

Jul 17, 2013

I've got a physical standby database and I'm now licensed for Active DataGuard. I would like to automate the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION command to be executed right after the STARTUP command so that whenever the database is started by our DBSTART script, it activates READ ONLY W/APPLY immediately after start without manual intervention. This is probably simple, but I figured I'd ask before starting to pore through documentation.....!

View 6 Replies View Related

Concatenating Two Select Statements

Oct 23, 2009

Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.

Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;

Select to_char(sysdate,'YYYY')"Year" FROM DUAL;

View 1 Replies View Related

Find Out What SQL Statements Currently Running

Feb 26, 2009

From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.

I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .

find out these details?.Which Data Dictionary i need to use ?.

View 2 Replies View Related

SQL Statements Behind Self Service Page?

Aug 29, 2011

I have a question regarding the SQL statements embedded behind the Self Service Pages in Oracle Applications.

Taking an example of Oracle E-business Suite, Is there a way to check what SQL statement is hard coded or embedded in a particular required Self Service Page?

View 1 Replies View Related

SQL Either Or Statements In Third-party Software

Dec 27, 2010

I'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.

I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:

USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith

The software automatically applies a SQL filter that begins with:

SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE

I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:

STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')

('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)

But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.

I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:

STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));

But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').

View 3 Replies View Related

SQL & PL/SQL :: Using $ Substitution In Select Statements?

Oct 11, 2011

Is it possible to have $ substitutions in Select statements ?

For example

Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00

For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?

View 2 Replies View Related

SQL & PL/SQL :: Syntax For Oracle Statements

Apr 26, 2011

The link from where I can get syntax of all the commands available in oracle?

View 6 Replies View Related

SQL & PL/SQL :: DML Statements - How Does Merge Works

Jul 23, 2013

My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.

View 3 Replies View Related







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