I am exporting using query parameter. I am trying to export subset of table using rowid.
SQL> select rowid , name from tab1;
ROWID NAME ------------------ --------------- AAAM0rAAEAAAAGMAAA sam AAAM0rAAEAAAAGMAAB sona AAAM0rAAEAAAAGMAAC rose AAAM0rAAEAAAAGMAAD chris AAAM0rAAEAAAAGMAAE san .................. .... .................. ....
I need trasform information from table A to table B. Table A is refreshed every day, Table B needs record all the information, including records nolonger existing in TABLE A. I would like to use one SQL to complete the operation. The merge works only for insert and update, but not for delete.
Here are the structure of the tables:
Create TABLE A ( course_number int; course_txt varchar(20); );
[Code]...
The record of tables will be as following:
On May 1, 2012 Table A. 100 math 200 english 500 social Table B. new 100 math May1, 2012 new 200 english May1, 2012 new 500 social May1, 2012
On May 2, 2012 Table A. 100 science 300 social
Table B.
update 100 science May2, 2012 delete 200 english May2, 2012 new 300 social May2, 2012 new 500 french May1, 2012
SQL> desc MTNLMONTHUSAGESUMMARY Name Null? Type ----------------------------------------- -------- ---------------------------- BILLMONTH VARCHAR2(7) AREA NOT NULL VARCHAR2(20) SESSIONS NUMBER USAGEMB NUMBER
I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.
So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:
ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER declare l_hObject NUMBER; l_Objddl CLOB;
writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:
Original table data:
Col1 Col2 Col3 Col4
Row1 a1 a,b,c 01 ON Row2 b1 d,e,f 02 OFF Row3 c1 g,h 03 ON
I want the above table to be transformed into below:
Col1 Col2 Col3 Col4
Row1 a1 a 01 ON Row1 a1 b 01 ON Row1 a1 C 01 ON Row2 b1 d 02 OFF Row2 b1 e 02 OFF Row2 b1 f 02 OFF Row3 c1 g 03 ON Row3 c1 h 03 ON
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64); CREATE TABLE department ( courses CourseList) NESTED TABLE courses STORE AS courses_tab; INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
[code]....
The query returns the correct data, CourseList that are not subset of any other CourseList of the table.
I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.
I was making some variations of this code :
select d1.courses c_1, d2.courses c_2 from department d1,department d2 where d1.courses<>d2.courses(+);
alter table shopowner.jobs add constraint uk_salary unique (min_salary, max_salary); insert into shopowner.jobs values (3, 'dba', 20002,55000, 'jobs');
Why does the above constraint not fail when one of these columns are not unique? It needs to fail both to fail the constraint.
3. what is a transaction? and how do you do it? how does deferred transaction look like?
4. How do you view constraints through isqlplus? 5. How do you drop multiple constraints at once? 6. When would you not use cascading delete? 7. How do you use create like cmd in enterprise Manger? I keep getting this error Failed to
commit: ORA-02264: name already used by an existing constraint even if you delete every constraint or rename ever constraint to anything.
8. how to make comments in isqlplus? 9. how do you add multiple insert values in at once in isqlplus?
I'm looking for a way to select 25% of a view.. I need to do this four time to get all the data. The size of the view changes constantly (I need to split my select because of heavy load on an application)
something like this:
Select * from MyView where [some condition that makes only 0-25% show ]
Select * from MyView where [some condition that makes only 25-50% show ]
Select * from MyView where [some condition that makes only 50-75% show ]
Select * from MyView where [some condition that makes only 75-100% show ]
I tried to search in the forum but found nothing.. maybe ROWID could be used to accomplish this somehow?
C:Documents and Settingsuser>sqlplus u1/u123456@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Pn Wrz 12 12:56:14 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Po│╣czono z: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production
SQL> update u1.tab1 set u1.tab1.col1=3 where u1.tab1.col1=1;
1 wiersz zosta│ zmodyfikowany.
SQL>
and now i can check which table is locked
SQL> SELECT lo.session_id, lo.OBJECT_ID, obj.object_name FROM v$locked_object lo 2 INNER JOIN Dba_Objects obj ON obj.object_id = lo.OBJECT_ID;
i have a procedure written below,it is compiled: ==================================================== CREATE OR REPLACE PROCEDURE myproc (vdept NUMBER) IS vsal number(10,2); CURSOR mycur IS
[code]...
but whenever i am trying to call this proc like below: ============================== begin myproc(10); end;
it is showing me error,i.e. :ORA-01410: invalid ROWID
I am trying to delete duplicates from table without using rowid.
here is data:-
create table test(col1 number(3),col2 varchar2(20)); insert into test values(100,'rocky'); insert into test values(100,'rocky');
[Code]....
I know i am perfoming dml on view. IT wont allow me to perform DML on view which contain columns with expression. IS there any way to delete duplicates without using rowid?
I'm placing this here since the only tool I have is TOAD to hack database. I have discovered a bad record with the system but as the date field is '0000' and the values are all hieroglyphics which I can't place in UPDATE statement. Only fields able to determine are that it's a journal entry. I tried the blow code only to be forced out of TOAD via ORA-03113: end-of-file on communication channel error is a generic error. How can I update the bad date fields with such limited access? Is the ROWID acceptable?
Update ft_os set ost_upd_dt = '06-dec-2010' where ost_tran_code = 'JEN' and ost_doc_ref = 'IS'
I am a developer and not a DBA and I need to find th correct query to find the exact rowid of the record locked on a table. This is for a RAC database and locked record can be from the web form in oracle application server. When I try to get the correct row id, I get the following error:
ORA-01410 - Invalid row id For the criteria, the output is Dbms_Rowid.rowid_create(1, -1, 36, 7845, 0), why I get a -1 for the ROW_WAIT_OBJ#?
Additional Information: The lock type is DML and the lock mode is: Row Exclusive, the table is locked and the program is web oracle forms executing.
I am executing the query in Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
How to accomplish getting the correct rowid? Below is the selection criteria I have:
I have a report linked to a form. The link column is currently ROWID and I want to change it to the Primary Key of the DB table, which is called ID.
I have done this before on a previous report/form where that ID was a display column and it works fine. Now I'm trying to copy what I did, which is this:
On the report page, go to Interactive Report attributes, scroll down to Link Column, and for Item 1, change the Name and Value.
The problem is that Name does not list P52_ID. If I type it in, it throws an error when I run the page.
I think I added ID, which is also a Display column, later on, so I wonder whether Apex has a refresh issue and can't "see" P52_ID.
update tab1 set col1 = ( select col2 from tab2 where tab1.id = tab2.id) table 1 has arnd 10,000 rows
table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.
I observed logical corruption in one of the database, while running select on some tables observed ORA-01410: invalid ROWID error. These errors or errors related to this logical corruption are not reported in alert log file.
Since our database is in NOARCHIVELOG mode and regular backups are not happening through RMAN (weekly cold mount point backup to tape), i was not able to use RMAN to investigate block corruption.
So used DBVERIFY on all datafiles of database to check the consistency of datafiles, and found DBV gives error for one of the datafile - 'Completely zero block found during dbv:' As i mentioned earlier, we are not taking regular backups using RMAN and database is in NOARCHIVE mode.
I have a Multi layer Mviews and Views which i use to load a table. There are three base level Mview which is accessed by 4 views ... which are in turn accessed by a view which is used to load a table.
Before the insert called for the table .. the base mviews are refreshed.
I get the "ORA-01410 Invalid Rowid" error while inserting in to the table and while refreshing the mviews. They don't occur every time and not together as well.
The Mviews contains table joins and the top level views which accessing these mviews does aggregation. Below is the mview query for one of the mview.
