SQL & PL/SQL :: How To Identify Specific Sequence Name Applied On A Table
Mar 1, 2011How to identify the specific sequence name applied on a table.
View 4 Replies
ADVERTISEMENT
Create Table Which Contains Details Of What Specific Seeker Applied
Oct 24, 2012I need to create a table which contains the details of what companies a specific seeker applied to when he logs in into his account.... Also, when an employer logs in, he needs to get the list of seekers who applied to him...give database schema required for such a situation asap.Say, the user has a primary key UID, and each employer has a primary key EID..
View 1 Replies View RelatedSecurity :: Audit Specific Statement On Specific Table By Specific User?
May 29, 2012I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
SQL & PL/SQL :: Identify Table Row
Nov 14, 2012Is it possible to find the schema name,table name and DB name if we know a value in table ?
View 6 Replies View RelatedSQL & PL/SQL :: How To Identify Which User Has Changed Table Structure
Jun 21, 2012a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.
View 2 Replies View RelatedSQL & PL/SQL :: Identify Department VP In Employee Interface Table
Mar 12, 2010I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name
1AJ Engineer BJ
2CK Analyst ND
3BJ Manager TR
5TR VP IT JD
6ND S Manager MD
7MD VP Telecom SK
8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
SQL & PL/SQL :: Table Update - Unable To Identify Procedure
Feb 21, 2011We have one table which is updated by some program but we are not able to identify the procedure, package or script which update it.
View 2 Replies View RelatedPerformance Tuning :: Oracle 10g - Identify Fragmented Table
Jul 18, 2010Need sql scripts which can identify the fragmented table in oracle 10g.
View 23 Replies View RelatedReplication :: Table A - How To Replicate Specific Row Transaction To Table B
Feb 7, 2013Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in *Table A*
Time: Lets say 15:00
A1 Just Updated @15:00
A2 Just inserted @15:01
A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication) @15:00
A1 (without updates)
A3
B1
B2
B3
B4
B5
B6
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
*Replication at 15:30*
Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
Questions:
1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
Displaying Records Between Specific Times On Specific Dates?
Apr 11, 2011I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?
Jan 25, 2013can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
SQL & PL/SQL :: Specific Data From Column Of Details Table
Jul 4, 2012In details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)
output should be it should take only first value from ().
Syntax For Setting Pctfree And Pct Used For A Specific Table?
Apr 25, 2013tell the syntax for setting pctfree and pct used for a specific table.
View 4 Replies View RelatedPL/SQL :: Return The Rows Of The Table Where Column Contains Specific Value First
Feb 5, 2013I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.
For Example:
Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
ORA-00600 - Application Unable To Access Specific Table
Dec 3, 2010Our oracle database has been running great. This past wendesday the application was unable to access a specific table. Sutting down and restarting the database seemed to have fixed the issue. Last night the database has been working since that restart on wednesday morning. Suddenly stopped working. We stopped and restarted the database again this time getting ORA-00600 error as shown below. We are able to make a connection to the database and can even access the tables in question. The application is complaining about the connection to the database. I understand that ORA-00600 is an internal error what I need to know is what the internal error code 549 actually is. I have tried googling to no avail.
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:47:20 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
ORA-00600: internal error code, arguments: [549], [], [], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\tieoduser>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:49:31 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
[code]....
SQL & PL/SQL :: Insert Into Table Without Using Sequence ID
Apr 9, 2012Is it possible to insert new id without sequence id.
For e.g. :
1 , 'BOLT'
2 , 'CHAIN'
i need to insert 3rd and 4th row as
3 , 'screw'
4 , 'driver'
without using sequence (autonumber gen).
PL/SQL :: How To Find Sequence For A Table
May 14, 2013I've got plenty of sequences. How can I identify that what are the sequences acting on a table?
View 8 Replies View RelatedSQL & PL/SQL :: Table Creation With First Column To Be Sequence
May 6, 2010I want to create a table, whose first column , patient_id, should be a sequence.
View 9 Replies View RelatedSQL & PL/SQL :: Assign Sequence Name During Table Creation?
Aug 22, 2011is it Possible to Assign the Sequence Name During the Table Creation.
View 1 Replies View RelatedSQL & PL/SQL :: Rows In Table With Sequence Number
May 17, 2012I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
PL/SQL :: How To Find Which Sequence Name Used In A Table - Redux
Sep 9, 2013Beginning with 12c, there is now a way to associate a sequence with a table. It's a new feature called an Identity column.
create table t
(some_id number generated as identity --< creates a system generated sequence
,name varchar2(30)
);
insert into t (name) values ('Smith');
insert into t (name) values ('Jones');
[code]....
How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I 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"
SQL & PL/SQL :: How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I 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"
PL/SQL :: Update Duplicate Records In Table With Sequence Value
Mar 25, 2013My table have duplicate records. I want to impose a primary key on that table. For that I have to replace duplicate values of a column with next value of a sequence.
View 2 Replies View RelatedPL/SQL :: How To Insert Sequence Numbers In A Table Column
Aug 23, 2012I am having the emp table :
empno enmae job
1111 ramu S.E
7658 VENU S.S.E
8280 GETA TRAINEE
alter table emp add sno number
for that sno i have to generate 1,2,3,...
PL/SQL :: Update Table Column Using Complex Sequence
Feb 6, 2013I use a sequence to generate an alphanumeric code and I need to update all rows of a table with that sequence. The sequence is created with this code:
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I have tried many things - most recently
Update t3_temp_table
SET mergecode =
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I get missing expression error.
It works fine if I use a straight s.nextval
SQL & PL/SQL :: Finding Next Record In Cyclic Order In Table Using Sequence
Sep 4, 2010I have a table as below:-
CREATE TABLE TEST_EMP_WIP
(
EMP VARCHAR2(20 BYTE),
WIP NUMBER,
MAX_SEQUENCE NUMBER
)
[Code]....
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 4
011 1 5
I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.
select emp_next from (
SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next
from test_emp_wip where wip > 0 )
where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0)
EMP_NEXT
010
After sometime, the record would look like :-
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 6 4
011 1 7 5
Now the next update should be done to the EMP '002' in cyclic manner.
tell me a way to do this. I can insert new columns if needed in table for calculation.
SQL & PL/SQL :: Using Sequence To Insert In Child Table Group By A Counter Column
Oct 1, 2011I found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
How Many Conditions Can Will Be Applied In Where Clause
Oct 3, 2013How many conditions can will be applied in where clause and what is the maximum length for where condition?
View 6 Replies View RelatedSQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation
Feb 22, 2010I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
