My solution to the following question update 10 rows. It should update only 6 records. Create a copy of the missions table called AM_X_442_2. For missions on this table that meet the following criteria:
1. within the 10 most recent missions
2. length of the first word of the mission code_name exceeds 7 characters
Change the security level to the highest security level found in missions of the same type.
UPDATE
AM_X_442_2 am
SET
am.SECURITY_LEVEL =
(
SELECT
MAX(amx.SECURITY_LEVEL)
FROM
AM_X_442_2 am
INNER JOIN
AM_X_442_2 amx
[code]....
I must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.
If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".
I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.
This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
1) its data 2) its constraints/indexes etc. 3) its storage parameters 4) data stored in EXACTLY same manner as in original table.
To avoid complications, the table in question is just a normal heap table without any partitioning involved. While
CREATE TABLE AS SELECT (or CREATE TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.
we are creating item_codes in our master table called om_item ,and then at one point of time every day we are shipping these newly created items based on date and tmie stamp as items.sql to our brance office and the person there will run these scripts there.Actually there are two persons involved in this process and i want to remove this manual intervention.All i need is to create a trigger for this insert statements and write it in text file as .sql.
create table om_item (item_code varchar2(20), item_name varchar2(60),item_cr_dt date) insert into om_item values ('a','aaaa',sysdate); commit; insert into om_item values('b','bbbb',sysdate+1); commit;
i have to display the 10 most costly missions. but if the 11th record has the same cost then it needs to display 11 records instead of 10. how can i do this?
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m SET m.security_level = ( SELECT max(m2.security_level) FROM missions m2 WHERE m2.mission_type_id = m.mission_type_id AND length(m2.code_name) > 7 ) WHERE m.mission_ID IN ( Select m3.mission_id From missions m3 ORDER BY m3.mission_id desc)
I want to create binary copy of control file every time I do backup. I am using sql "alter database backup controlfile to ''+FRADG/TESTDB/controlfile/controlfile.ctl'' "; in my rman backup script. If I run it second time I got errors as it does does not allow to overwrite existing one.
i could copy data from tableA to tableB with the scenario below
1) Currently we have no unique key / primary key defined on both of this tables, due to the nature of data, so insert/update will not work 2) We cant truncate/insert as user will be accessing this tables when the copying process takes place, so we dont want to end up having a scenario where the table does not contain data at a certain time
Will materialize view full refresh work in refreshing a table so we could avoid the problem faced in point 1 & 2.
I have two tables namely PERSON and WIFE. I want to make WIFE's data available in PERSON table while keeping entries of WIFE maintained and at the same time adding some the values of PERSON against the data of wife.
PERSON Table PK NAME ADDRESS IS_MARRIED 1 John ab city Y 2 Varvatos cd town N 3 Smith ef town Y 4 Henry gh city Y 5 Lynda gh city Y
WIFE table
PK PERSON_ID (FK) NAME 1 1 Alice 2 3 Rosy 3 4 Lynda
Now i want to copy data of WIFE table into PERSON table like this PERSON table
PK NAME ADDRESS IS_MARRIED 1 John ab city Y 2 Varvatos cd town N 3 Smith ef town Y 4 Henry gh city Y 5 Lynda gh city Y 6 Alice ab city Y 7 Rosy ef town Y
As in the given example you might have noticed that ADDRESS of wife is same as of her spouse and same goes for IS_MARRIED column. Moreover, the PK is also not duplicated. How to go about this?
Another important factor is Lynda already exits in PERSON table, therefore, i certainly don't want to duplicate her entry.
How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.
Triggers for oracle database. What I am trying to do is copy original data from one table to another table prior to an update, insert, or delete occurring. Basically we are trying to keep a transactional history, with out having to restore the data. Here is what I have created to date, however it is not executing consistently.
CREATE OR REPLACE TRIGGER DATA_COPY BEFORE INSERT OR DELETE OR UPDATE ON "DB1"."TABLE_1" [code].......
I have 2 tables with same no of column and range partition based on date.At the end of month i want to copy the data of one table to another table.Instead of copy the data i want to copy the data of one partition completely to another table partition ..
In MS Windows, if I copy a file and paste it into the same folder, I get a copy with the text 'Copy of' in front of the file name. If I paste it again, I get another copy with a different version number.
E.g. sqlnet.log Copy of sqlnet.log Copy (1) of sqlnet.log Copy (2) of sqlnet.log
I was wondering if I could copy existing rows into a table and do the same thing?
insert into tst_srch values (1,'USER1','SRCHA'); insert into tst_srch values (2,'USER1','SRCHB'); insert into tst_srch values (3,'USER1','SRCHC'); insert into tst_srch values (4,'USER1','SRCHD'); insert into tst_srch values (5,'USER2','SRCHC'); insert into tst_srch values (6,'USER2','SRCHD'); insert into tst_srch values (7,'USER2','SRCHD_1');
Could I write a procedure like copy_searches('USER1','USER2') that would copy all USER1's searches to USER2 - including renaming any duplicates.
i have a table with 15 coulums and containing millions of rows which is being updated everyday.Now i have created a similar report table with only the coulums i need to report on from the main table.what plsql script or if there is any better alternative do i need to write to copy the data from the coulums i need from the main table to the new report table. the new report table will be be updated every 01:00am with the data coming from the main table and the update is automated.
I need to copy one table which is having NCLOB column into our new DB(11g) from old DB (10g). As this table having huge records (1 crore) it is difficult to use direct insert statement (INSERT INTO ... SELECT * FROM @remotedb). I don't have DBA privilege to use loader.
I renamed 100 tables and recreated them, now I need to copy supplemental login information from renamed table to new tables. Environment is oracle 10G.
I have to copy a table from oracle 8 to oracle 10 - dblink don't work between 8 - 10 - can I dump table ? - when I try to use sqlload via TXT file, I have error messages due to a column containing 'special' caracters ( ,..) and line too long (splitted in several rows)
i have a large OLTP database and we are doing table stats copy amount subpartition to save the load on system. while doing an copy default subpartition stats: I see the following error:
SQL> exec DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); BEGIN DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); END;
* ERROR at line 1: ORA-03113: end-of-file on communication channel
I have a table called "Subjects" which lists subjects to match with notations in another table I have created a simple sequence (CREATE sequence subjectid) to created the subject id for the table. But I notice that if there is a skip in the date, the sequence increments automatically when I am not even using it. It even appears to be incrementing even when I am not doing any database activity.
This is not an issue of data integrity, because the values in the subject_id column do not need to be sequential, they just need to be unique. But it really has me curious. I created another table called "keep_track" to keep track of what is happening:
I have a procedure in the live environment , which operate in 2 mode.
1 regular load at 10 min 2 nightly
The code is same except they set the last loaded time for both in 2 dfferent rows, so that next time run will get incremental data.
I can trace this procedure on mode 1 that is is scheduled but unable to find how the 2nd one is getting trigger every night. there is no jobs corresponding to that. Is there any way i can find which method is trigger the procedure during night.
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER INTO v_schema_name FROM ALL_OBJECTS WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
A Java program would call a sequence of 5 PLSQL procedures, and they all need to participates the Tranasction defined in Java.
What do I do for the transactions inside PLSQL to ensure all 5 pariticpate in the Java transaction? I suppose I just do not specify any COMMIT in the PLSQL, but that probably would not work as PLSQL would COMMIT by default after exiting the proc?
Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?
