SQL & PL/SQL :: Update Existing Table

Apr 24, 2012

I want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table

the output will shows like below

empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012

the forth one column is in date format and I want to update table with respect to order by the column start_date

View 1 Replies


ADVERTISEMENT

Server Utilities :: How To Update Existing Table Using SQL Loader

Sep 30, 2011

I am using a shell script to load unix content to a database.I have captured the unix data to a csv file and I am using a sql loader to inser that csv data to database. following is my ctl file contents.

[b]load data
infile data.csv
into table AVS_LOGS
fields terminated by ','
(
RUNDATETIME,
SERVER,
DIRECTORY,
FILENAME,
LASTUPDATETIMESTAMP
) [/b]

and I am using sql loader command in unix that is [b]sqlldr $CLOGIN control=control.ctl log=test.log[/b]

But this is working only if the table is empty.Now I am looking for something where I do not need to delete the data from table each time. it should update the table.

View 5 Replies View Related

Performance Tuning :: Unable To Update Newly Added Column In Existing Table

May 21, 2013

I am facing some challenge while running update query on newly added column in existing table.

Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1

No of records in table : 12572770

Below are the step i followed.

1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.

SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)

[Code]...

Table altered.

SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;

INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24

Problem faced & My analysis

1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.

Below are the queries completed successfully

Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

[Code]...

Below are the queries hanging in database

Update Testtablename

Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Update Testtablename

Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Below is character set in database

SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA

[Code]....

View 15 Replies View Related

Forms :: How To Update Current Row Comparing It With Existing Value

Jul 14, 2013

I have master-detail form I would like to update the emp1 table on the base of current value of dept table deptno and emp table ename .

update emp1
set ename = :emp.ename
where ename =
(select ename from emp e
where e.ename=:emp.ename
and e.deptno=:dept.deptno);

I try the above query but it did not able to update emp1 table.

View 12 Replies View Related

Creating Table From Existing Table In Another Schema?

Jan 4, 2009

I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?

View 4 Replies View Related

Create Temp Table From Existing Table

Sep 2, 2010

I want to create temp table, for this i am using:

CODEcreate global temporary table help_temp
as
select * from help;

but this is creating only the table structure, not copying the table data.

View 5 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:

If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.

Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?

View 4 Replies View Related

SQL & PL/SQL :: How To Add Row Dependencies In Existing Table

Jul 20, 2012

How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..

View 2 Replies View Related

SQL & PL/SQL :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

SQL & PL/SQL :: Create Trigger To Update Table B After Update On Table A

Jul 21, 2011

I have table test1(id,name) and table test2(id,,name)

Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.

So I wrote this trigger but its not working

create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/

View 9 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/

View 3 Replies View Related

Getting Timestamp For Existing Records In Table?

Nov 15, 2010

I need to get the timestamp for all the existing records in my table...I am having one user definition field, is this possible?

View 4 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

SQL & PL/SQL :: Cluster Creation On Already Existing Table

Feb 20, 2012

I have table property and flat , in both of these tables I have cvp_id common colomn .Now I want to add cluster on this colomn so how can I add cluster to table which is already exists.

View 3 Replies View Related

SQL & PL/SQL :: Create Partition On Existing Table?

Aug 13, 2010

I need to create partition on the non partition table without dropping a table.

Range partition on stage tables and hash partition on fact tables.

View 4 Replies View Related

SQL & PL/SQL :: CREATE Table From Existing Tables

Oct 31, 2013

I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.

Below is example

TABLE1

ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3

TABLE2

BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33

COMBINED_TABLE

ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33

I tried below query but no luck. It gives below error:

Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum

Error : ORA-01747:"invalid user.table.column, table.column, or column specification"

View 7 Replies View Related

PL/SQL :: Creation Of A Table After Checking Whether It Is Existing

Apr 22, 2013

declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception

when others then

vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception

when others then

dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;

View 14 Replies View Related

Compress Existing Table Which Is Not Partitioned

Aug 1, 2013

Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.   

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"

View 3 Replies View Related

How To Add Sequence Column To Existing Table With Records

Dec 8, 2012

I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,

CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;

I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,

ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;

But I get an error with this,

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"

View 2 Replies View Related

Partitioning - Switch Partition Key On Existing Table?

Jan 23, 2011

I recently started working with legacy code and noticed that some huge tables (5 years worth of data, don't have more details on me right now but can post later if needed) are partitioned based on time sequence number column while majority of queries are done based on time (different column). Queries performance is degrading and I'd like to try to modify partitioning and run some tests to evaluate performance improvement.

My only concern is with so much live data I have to come up with solution on how to switch partitioning with the least impact on applications running 24 x 7. Something you have done in the same situation and it worked?

View 1 Replies View Related

SQL & PL/SQL :: How To Add Sequence Column To Existing Table With Records

Dec 8, 2012

I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,

CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;

I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04.

I tried using the AUTO-INCREMENT option,

ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;

But I get an error with this,

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"

View 4 Replies View Related

SQL & PL/SQL :: Adding Column In Middle Of Existing Table

Sep 6, 2010

below query.

1) How to add a new column to the existing table's particular position, instead of atlast.

2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?

View 3 Replies View Related

Possible To Compress Existing Table In Oracle Database

Nov 29, 2012

it's possible to compress the existing table in oracle database directly.

View 1 Replies View Related

Select Privs On Table To Existing User

Mar 18, 2013

I have granted select privs on below tables to an exisiting user.grant select on WEB.ALMSTAT to nms...I would like to view the privs that i have granted to nms user?

*"In production, nms user will need the ability to read the following tables:*

*WEB.ALMSTAT*
*NET.ASSET*
*NET.LOCATIONNAME"*

View 2 Replies View Related

PL/SQL :: XML Errors While Partitioning Existing Table By DBMS_REDEFNITION

Jan 28, 2013

I am trying to partition an existing table through DBMS_REDEFNITION. Following are the steps that I have taken and the error I have got.

1. Creating a table to be partitioned.

CREATE TABLE SO33070_ORIGINAL
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE,
PRIMARY KEY (SERIAL_ID)
);

Success

2. Checking if the table can be partitioned

DECLARE
v_name VARCHAR2(256);
BEGIN
SELECT sys_context('userenv', 'current_user') INTO v_name FROM dual;
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'SO33070_ORIGINAL', dbms_redefinition.CONS_USE_ROWID);
END;

Success

3. Creating a duplicate table

CREATE TABLE SO33070_NEW
(
SERIAL_ID NUMBER(15,0),
INSERTED_TIME DATE DEFAULT SYSDATE
)
PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)

Success

4. Starting the redefnition process

EXEC DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'CDS_USER', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
Success

5. Copying the dependents

DECLARE
num_errors NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'CDS_USER',
orig_table=>'SO33070_ORIGINAL',
[code]....                                                                                                                                                                             

View 3 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

Jul 19, 2011

I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).

View 4 Replies View Related

SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?

Dec 19, 2011

I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.

GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'

[code]....

View 5 Replies View Related

Convert Existing Table With Varchar Column To A Clob?

May 8, 2013

how to convert existing table with varchar column to a clob

View 1 Replies View Related

SQL & PL/SQL :: Can Change Existing Table Column Data-type

May 18, 2011

i have one table emp in this table i have the column eno,ename,hiredate and i have data also in this table.

my eno colum data type is number so now i have to change this colum data type from number to varchar2.

if yes how can when i am trying to change this colum data type i got this error

Failed to commit: ORA-01439: column to be modified must be empty to change datatype.

View 1 Replies View Related







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