SQL & PL/SQL :: Updating And Deleting Of Data

May 21, 2010

I have been using MSaccess to do some updating and deleting of data.I have 2 tables, 1 with data, 1 with the criteria to change, 2 colums (FINDWORD and REPLACEWORD).

The main table has lots of data, and the SQL query im using in access looks like this:

UPDATE TABLE1, tblFindReplaceWords SET CleanList.COLUMN-NAME = Replace([TABLE1].[COLUMN-NAME],[tblFindReplaceWords].[FINDWORD],[tblFindReplaceWords].[REPLACEWORD]);

how would I go about doing the same thing in orcale?

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: When Where Current Clause Is Used In Updating Or Deleting In Loop

Jul 1, 2013

when 'where current clause' is used in updating or deleting in loop.... is it required to write commit in the loop or it will auto commit?

View 2 Replies View Related

Best Way Of Deleting Data In Millions

Feb 11, 2013

I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.

best way to delete such huge data count.

View 2 Replies View Related

Data Guard :: Deleting Logs In Standby

Jan 27, 2011

I've a primary database and a physical standby.Logs are shipping perfectly from primary to standby.The logs that are applied on the standby are getting stored in a mount point(LINUX-/opt2) which consumes more space(160 GB). Will that be right if I go ahead and delete them?

My rman configurations in primary is:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 4;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

Nov 2, 2012

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 10 Replies View Related

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

Nov 2, 2012

I have scenario here.

I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

Below is the example.

I have created a table employee in abc schema which has two values.

EMPLOYEE
ABC
XYZ

In the above scenario the abc user can only fire select query on the EMPLOYEE table.

SELECT * FROM EMPLOYEE;

He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.

View 6 Replies View Related

Server Administration :: How To Reclaim Space After Deleting A Data File

Jun 30, 2010

i have a tablespace with a datafile of 20g. now by mistake i delete the datafile and then try to delete the tablespace from EM but i got an error which says that data file is not present to delete

Now initially after deleting the file physically so then i check space by applying df -ah at os lvl so it didn't reclaim the space now i try to delete the tablespace from em so it gives me the above error. This might be due to tablespace existence. so how can i reclaim the space.

View 14 Replies View Related

SQL & PL/SQL :: Updating Multiple Data

Aug 19, 2013

UPDATE PMAPS_Z_DEMAND_MASTER_DATA_TMP C
SET PACKAGE_NAME =
(SELECT DISTINCT
A.PACKAGE

[Code]...

i wanted to update data in PMAPS_Z_DEMAND_MASTER_DATA_TMP table but i kept getting "more than 1 value returned in sub query" error, i know what it is but i don't know why it is happening here.

View 8 Replies View Related

Updating Long Data Type Column

Aug 31, 2010

I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.

I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.

View 2 Replies View Related

Creating Self-updating Views With Data Selection?

Aug 4, 2011

I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.

I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).

View 1 Replies View Related

SQL & PL/SQL :: Locking Views (rows Of Data While Updating)

Nov 10, 2011

I have a set of rows based on a complex view from multiple table.

I will be updating some of its columns from front-end . Is there any possible ways to lock those rows of data while updating and no other users can update it;

View 5 Replies View Related

Forms :: Updating - Insert Some Data In Transaction Table

Feb 1, 2011

i want to insert some data in the transactions table on after insert trigger. Which trigger should i use on form level to accomplish this task.

View 4 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

ODP.NET :: Updating Dataset / Datarow If Data Is Populated From Stored Procedure

Mar 11, 2013

I am using:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Microsoft Visual Studio 2010 : VB Express
Microsoft .NET Framework : Version 4.0.30319 RTMRel

When I populate dataset using select query and use OracleCommandBuilder and update dataadapter, data is saved in database.

But if I put same select in store procedure and use this to populate dataset then data is not updated in db.

I get this error

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

at da1.Update(ds1, "req") where I am going wrong.

Dim conn As New OracleConnection
        Dim da1 As OracleDataAdapter = New OracleDataAdapter
        Dim ds1 = New DataSet
        With conn
            If .State = ConnectionState.Open Then .Close()

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Trigger For Deleting

Jun 4, 2010

I have a table which has a trigger defined as such on the Main table.

before INSERT OR UPDATE OF USER_NM,COmpany_ID,UPDATED_BY_USER
OR DELETE ON MAIN TABLE OLD AS OLD NEW AS NEW
FOR EACH ROW

When I insert the 1st row (by user Scott)

MAIN TABLE

USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------
ABC1Scott

In the audit table I have this information:

USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert

When an ADMIN user (other than Scott deletes the above row)

MAIN TABLE:

USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------row deleted....

In the audit table i have 2nd entry as:

USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
ABC 1 ScottDELETE

Here the updated_by_user is Scott and not ADMIN, even though I am doing the following in the trigger.

UPDATED_BY_USER := sys_context('USERENV','OS_USER')

I want to show the user who has deleted the record which should be ADMIN.

Since the user deleting may not be the samae as the one who inserted.Any clue?

View 3 Replies View Related

Deleting All Tables And Constraints

Oct 19, 2006

After creating all the tables and the constraints, and inputting data to the table.. i want to delete everything. i try using drop table but it doesn't get rid of the constraints.

View 10 Replies View Related

Deleting Tables From Productions DB?

Jun 30, 2010

looking at my oracle DB schema I see that there are considerable number of tables that appear to be backup(!!) of other tables and don't ask me the reason why would some one create backup/temp tables in production.

All the tables almost occupy 7GB of space and I would like to get ride of them. Before I get ride of them I would like to see when the table was last accessed for any purpose like any DDL/DML statements and any Select statements performed on the temp/backup tables as well. Is this kind of information readily available in any SYS tables or should I write a trigger to get the details going forward.

View 4 Replies View Related

PL/SQL :: Deleting Duplicate Records

Dec 20, 2012

I have a table and it's having duplicate records.

for one particular employee, he is having multiple records with the same data in the table

EMPNO    ENAME     JOB     SAL     DOB
-------------------------------------------------------
1               A           X        100     1956
2               B           Y        200     1974
1               A           X        100     1956
3               C           Z        300     1920

[Code]....

like this am having multiple times the duplicates.

I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.

query which am using to delete the duplicates is

DELETE FROM Table1 a 
WHERE ROWID IN (SELECT MAX(ROWID) 
                FROM Table2 b
                WHERE a.ID = b.ID); 

it is deleting only one row but I want to delete 4 records out of 5 and keep one record.

View 12 Replies View Related

SQL & PL/SQL :: Deleting Partition Of A Table

Nov 19, 2012

create or replace
Procedure ReadingsPurge
As
v_sql varchar2(500);
v_date date;
p_count NUMBER;

[Code]...

-- Code below drops partitions that are older than the NoOfDays Parameter
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;

[Code]....

Above code is compiling successfully.

After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error

Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

View 2 Replies View Related

Deleting Huge Records From Table?

Apr 29, 2013

Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.

Method I have used:

1. Created Temp table

2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.

delete from B where exists (select 1 from temp where b.col1=temp.col1);

3. why it is taking too much time for deleting records in table A.

View 5 Replies View Related

Deleting All Backed Up Archives Using RMAN?

Jul 5, 2012

I used 'delete input' to delete all the archives backed up.

I am storing archives in dest1 and dest2...i.e, in two locatiions in production.

Although archive in dest1 is getting deleted I am unable to delete those in dest2. how to delete archives in dest2 too.

View 3 Replies View Related

SQL & PL/SQL :: Script Hangs When Deleting From Table

May 24, 2010

I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...

DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;

Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.

I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.I am using SQL Developer but I have found the same problem when running the script from SQL Plus.This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).There is only one row in COM_ISSUE.

CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
[code]....

View 9 Replies View Related

Server Administration :: Deleting Archive Log In DR

Mar 6, 2012

I have a small question to be clarified. Is there any way to find out the "Applied Archive log files" in DR and "Deleting them through .

View 1 Replies View Related

Forms :: Alert While Deleting The Record

Mar 20, 2013

I want to create one alert while deleting the record from the custom form. So i have written the below code in the when-remove-record trigger.

DECLARE
alert_button NUMBER;
BEGIN
IF :XXWIPRELFRM.PRINTED = 'Yes' THEN
alert_button := SHOW_ALERT ('DELETE_ALERT');

[Code]..

When i click on the delete(X) symbol in the form, it is showing alert but when i click on "No" also its removing the record..what i need to write.

View 7 Replies View Related

Forms :: Deleting Records From Block

Aug 2, 2011

I want to delete records from block in the form.could you explain where(in which trigger) should i write set_block_property.

View 9 Replies View Related

SQL & PL/SQL :: Deleting Master / Detail Record?

Oct 13, 2011

I want to delete Master / Detail Data through cursor between date 01-02-2010 till 10-02-2010. Problem is in Detail I dont have date column in detail. But I have to delete Master and Detail record with desire date. I have made a cursor but it delete only detail record i want to delete master record too.

Master Table
M_NO CHAR (12) NOT NULL,
REMARKS VARCHAR2 (200),
CANCEL_YN CHAR (1) NOT NULL,
M_DATE DATE NOT NULL,
PRIMARY KEY ( M_NO ) ) ;

Detail Structure

M_SNO NUMBER NOT NULL,
ACCOUNT_CODE CHAR (19) NOT NULL,
CANCEL_YN CHAR (1) NOT NULL,
M_DESC VARCHAR2 (200),
DB_AMT NUMBER,
CR_AMT NUMBER,
M_NO CHAR (12) NOT NULL,
PRIMARY KEY ( M_SNO, M_NO ) ) ;

create or replace procedure test as
cursor md_cur is
select m_No from master where m_Date
between '01-02-2010' and '10-02-2010';

[code]./....

View 9 Replies View Related

Deleting Archive Logs From Disk

Feb 13, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE     11.2.0.3.0     Production"
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

We are in the process of setting up our backup policy. After the Archived Logs have been backed up, we need to delete them after 7 days. Also the actual files on disk.

RMAN does not delete the Archived Logs from disk.

View 6 Replies View Related

Objects :: Deleting A Row With Object Column

Jul 4, 2012

I have a table, a superclass and several derived classes.

the table definition looks like this:

/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
     oid               number,
     transactid          varchar2(200),
     sessionid          number,
     hash               number,

[Code]....

ERROR at line 1:

ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing optionsThe error occurs on 11.2.0.3.0 - 64bit as well.

As a workaround i'm not deleting the rows, but setting the stored objects to null. At the end of the routine i can truncate the table with the objects since truncating works. I'm not the administrator of the database, therefore i may not change any settings.

Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this workaround (simply don't truncate at the end), but leaves a lot of crap lying around.

View 4 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

Sep 29, 2011

How can I delete the duplicate combination of records from the below table.

CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);

[code]....

The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.

Expected result after deletion

GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26

View 5 Replies View Related

SQL & PL/SQL :: Deleting Multiple Records Based On Conditions

Jun 28, 2010

I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.

Sample Table Script:

CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...

View 8 Replies View Related







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