I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?
View 9 RepliesI am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
i am trying this query
SELECT
CASE
WHEN TYPE = 'COUNTRY' THEN (SELECT CODE FROM LIST_T)
END mi
FROM LIST_T
and in that subquery i am returning two or more than records so it is giving me the error like ORA-01427: single-row subquery returns more than one row
how do i solve this error and i must to fetch two or more records from the subquery if it possible in any other
I have written a trigger as below:
CREATE OR REPLACE TRIGGER LOGONTRIG
AFTER LOGON ON DATABASE
DECLARE
v_temp NUMBER;
V_bypass_trg_flag VARCHAR2(1);
[code],...
This trigger got successfully compiled. and when am trying to connect to the db with other user its giving me error "ora-06512 : at dbms_session".When i grant dba privillage to that user am successfully able to login to db.
i'm not able to open layout in the custom form.automatically it will be closing and giving this error. Oracle Forms Designer has encountered a problem and needs to close.
View 5 Replies View RelatedI am attempting to make a job on an active database and I keep getting the error "An error occurred checking if the script file directory exists. Correct the error and try again. Error message: Agent Unreachable"
I can't find any information about the error on the logs and I don't knwo what to do about the error. I really need the script the job makes but how to get it.
How we can resolve :
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
We just upgraded to APEX 4.2.2.00.11 and we are getting ORA-24247: network access denied by access control list (ACL) errors form apex_mail. This had been working correctly prior to the upgrade.
View 4 Replies View RelatedI am using below command to import a schema using network link. Command is :
impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"
This command is giving below error
LRM-00116: syntax error at 'view:' following '='
When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:
UDI-00014: invalid value for parameter, 'exclude'
We make a 'SELECT' and then create a file with the results and send it by email... what we want to do is , if there are no results from that select , not sending the email neither creating the file..
View 5 Replies View RelatedI would like to create a trigger on a table, but only if the table exists.
This is what i've got so far:
create or replace function tableExists (tablename in varchar2) return boolean
is
c int;
begin
[Code].....
This would give me the error:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update
I executed the following statements
SQL> CREATE OR REPLACE Function test
2 ( name_in IN varchar2 )
3 RETURN number
4 IS
5 cnumber number;
6
7 cursor c1 is
[code]....
how I can view the full statement of creating the function.
I am writing the following PLSQL code-
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........
The new table used above has the following structure
Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE
However I get the following error on execution and am not able to make out why?
BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3
error while creating a catalog db
error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist
RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog
I have create the following procedure to create individual jobs dynamically on each schema.
CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN
[Code]....
While executing this procedure I am getting the below error.
PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3
[Code].....
I am trying to create my own error message but when ever i used sp_addmessage it gives error sp2-0734 unknown command biggning... what to do?
View 12 Replies View RelatedI am creating prim and phy standby on same WIN machine. I have done with all settings. I can do TNSPING to both oracle_sid.I am trying to connect as sysdba to UP the phy standby. But it gives below error:
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
So i tried creating password file for phy standby. But gives below error:
Unable to find error file %ORACLE_HOME%\RDBMS\opw<lang>.msb
I am running it from proper dir.
Am trying to create a materialized view as below
SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
[code]....
ERROR at line 12: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
While creating temp backup table it shows error ORA-00904 'invalid identifier'
CREATE TABLE xxom_valvelink_vlvs_temp AS
SELECT msie.inventory_item_id,FDFC.application_column_name,
flv.tag,
[Code]....
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]
CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
[code]....
I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.
When trying to create package I receive this error...[Error] PLS-00103 (8: 16): PLS-00103: Encountered the symbol "." when expecting one of the following:;
pointing to "." in the last line "END nondrg_prod.Readmissions;"
CREATE or replace PACKAGE nondrg_prod.Readmissions AS
TYPE Readmits IS REF CURSOR RETURN READMISSIONS_DATA_MH%ROWTYPE;
PROCEDURE open_rdmh (rdmh IN OUT rdmhCurTyp);
END Readmissions;
[code]....
creating a sql table im getting error " ORA-00907: missing right parenthesis"
CREATE TABLE Campaign
(campaign_id NUMBER(5) PRIMARY KEY,
Account VARCHAR(20) NOT NULL,
SalesLead ID,
SCLead ID,
[code]....
the problem is "ID" and "ID+R", i need these to conect with other sql tables
H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error
For Example My view in XXX Instance
CREATE OR REPLACE FORCE VIEW xxx.sample
(
First_name,
last_name
)
as
select "F_NAME",
"L_NAME"
FROM EMP;
When try to copy and compile the same view in my YYY Instance i am getting error
while creating database*(windows server 2008 R2 version and 11g r2)*
1.DIIM ERROR OCCURS
2.then i started all the oracle services on
3.then igot ora-01092:oracle instance terminated disconnection forced
4.ora-00450:background process'qmnc' did not start
5.ora-00444:background process"qmnc" failed while starting
6.ora-maximum number of process 0 exeeded
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
CREATE TABLE special_rate
(
rate_id VARCHAR(3),
rate VARCHAR(5),
rate_expiry_date NUMBER(12),
PRIMARY KEY( rate_id ),
FOREIGN KEY( contract_id ) REFERENCES contract(contract_id)
[code]....
we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema.
In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1:
select * from user1.abc@DBL1;
we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below:
create materialized view abc_mv
as
select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below
select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
I have created a database in Oracle10g which is working fine. Now when I try to create another the system gives the following error.
ORA-12560: TNS protocol adapter error.
I am creating a New User by using our customized Oracle Form for Creating a New User.
I am ting the Bellow errors:
-604-ORA-00604 : error occurred at recursive SQL level 1
ORA-200001 : failed to create user,please contact dba for support.
ORA-06512: at line : 16
I am using ORacle Forms 10g as a Front End and Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production as Back end.