SQL & PL/SQL :: Oracle Table - Finding Transaction Logs?
Dec 9, 2010How can we see the history of transactions on a table.
For eg, If I have a table called TEST123, I would like to know whatever SQLs fired on that table on today.
ADVERTISEMENT
PL/SQL :: How To Create Transaction Table In Oracle
Mar 15, 2013in oracle transaction table /temporary table /at the time of movement data insert
how to handle these table in merge no primary key
Replication :: 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?
Transaction To Be Updated To Master Table
Feb 16, 2012I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.
Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y
excel spreadsheet
col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r
SQL & PL/SQL :: Transaction Table - Index Recreation?
Aug 3, 2010There is a transaction table which becomes very slow after 3-4 months in spite on regular weekly analyze and index recreation.
But once we drop the constraint (PK) and recreate it again it becomes ok. The same doesn't happen even when we rebuild the index.
Group By Month - Query A Transaction Table?
Oct 19, 2010sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
View 2 Replies View RelatedSQL & PL/SQL :: Global Temporary Table And Autonomous Transaction?
Jul 15, 2013The actual flow, works on this way:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).
(X isn´t important on the new flow)
Now, i need to add some steps on the normal flow:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).
The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.
Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).
I need:Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.
Forms :: Updating - Insert Some Data In Transaction Table
Feb 1, 2011i 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 RelatedFinding Primary Key In A Table?
Oct 4, 2007Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies View RelatedOracle Trigger On Transaction Commit
Nov 29, 2007I am working with an oracle table that is populated by a trigger on another table.. So Table A is an audit of table B. The trigger also uses sysdate to populate a modification Date column on the Audit table.
I was using this modification Date column in a query interface to get changes that happened on the main table after a certain date/time.
The problem is that there is an application that uses transactions to write to table B and sometime this transaction may not be committed for over a minute so the modification Date is not a reliable way to query the table for changes after a certain time.
Is there a way to update the trigger/create a new one where the sysdate that gets written to the audit table is from when the transaction is committed, not when the transaction starts?
Oracle Crash Before Uncommitted Transaction
Jul 10, 2012Consider a situation:
There are two users 'A' and 'B'
account number of A: 001234
account number of B: 002678
Suppose, if A wants to transfer $1500 to B's account
update cust_acc
set balance = balance - 1500
where
account_no = '001234';
[code]....
Assume that, oracle crashes somewhere between first commit and second commit.In this case, A's total money is deducted by $1500..But $1500 is not yet transferred to B's account.How will oracle keep track of these information?
SQL & PL/SQL :: Finding Nth Maximum Or Minimum From A Table?
Sep 17, 2011how can i find nth maximum or minimum salary from a employee table? here i want only one row as output.
View 16 Replies View RelatedSQL & PL/SQL :: Finding Percentage Using Query From Table Data
Jun 23, 2010SL_NoStatus
191502Implemented
191690Implemented
190998Implemented
191346Implemented
190541Implemented
[Code]..
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case:
lets consider,
A->count(Sl_NO)
B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
SQL & PL/SQL :: Finding Out If A Table Column Has Special Characters
Jul 15, 2010I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
CREATE TABLE CHECK_SPL_CHARAC
(ADDRESS VARCHAR2(100));
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC%ABC');
INSERT INTO CHECK_SPL_CHARACVALUES ('ABC_ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC?ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC*ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('1234');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('AB C');
INSERT INTO CHECK_SPL_CHARAC VALUES ('12 34');
COMMIT;
I need to get the first 4 rows alone in the sql output. I do not wan to get the last 2 rows with space.
SQL & PL/SQL :: Finding Child And Parent Rows In Table?
Jun 6, 2011I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E
A-->B-->C-->D
F-->G
H
When i will pass d as node it will return c,b,a,e,f,g,h
SQL & PL/SQL :: Finding User Who Made Changes In Table Structure Or Schema
Sep 21, 2011i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?
following Output needed
userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername
SQL & PL/SQL :: Finding Maximum Value Of Two Independent Columns Of Single Table
Jan 18, 2012The challenge I am currently facing is finding the maximum value of two independent columns of a single table. Here is the structure of the table:
[FiscalYear] [AccountingPeriod]
------------ ------------------
[2012] ------------- [2]
[2011] ------------- [12]
[2012] ------------- [1]
What I need to be able to do is to:
1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column Accounting Period.
I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). Is it possible to run this query on Oracle with only one pass through the table where the returned result will show 2012 for FiscalYear, and 2 for Accounting Period?
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 :: Finding Record In Table Having Value For Maximum Number Of Columns
Nov 25, 2011if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
SQL & PL/SQL :: Mac Address Finding Using Oracle
Sep 26, 2011we can find the IP address in V$session table. Is it possible to find the mac address of user's computer?
View 13 Replies View RelatedClient Tools :: Oracle Services For Microsoft Transaction Server?
Apr 2, 2012I want to use SSIS,when i use oracle 10g as a data source to SQL Server 2008 as Destination, it gives error ora-01017 invalid username/password logon denied, i did normal oracle installation and give full privileges(sysdba or sysoper) to the user(sys,system...),when i check connection it shows test connection succeeded but when click on next it gives above error.
View 3 Replies View RelatedSQL & PL/SQL :: Finding Dependency Of Function In Oracle
Nov 26, 2012Is it possible to find the dependency of a Function whether it is used somewhere in the package or procedure or some other functions?
Suppose I have function named get_valid_loc , Now my question is how to find out in which place this function has been used.
How To Use Perl DBI Script To Poll Logs From Oracle Databases
Jul 27, 2010I have 2 separate servers each with oracle database installed.I am trying to pull oracle logs from these 2 databases to my main linux server using perl DBI. On this linux server I have 1 dbipoll.pl script and 2 wrapper scripts that pass in parameters to dbipoll.pl.
Here is the sample wrapper script that I've used to call dbipoll.pl(separate script with different parameters for each databases used):
CODE#!/bin/sh
#
export ORACLE_HOME=/ora-main/app/oracle/product/11.1.0.6/db
export ORACLE_SID=rmdev1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
#
[code]....
As you can see,the script should retrieve logs and write to oracledb1.log and update the countfile1 with the latest timestamp.The wrapper scripts are scheduled to run at 5mins interval.
The first wrapper script,which I've named as call_dbipoll1.sh runs fine. I am able to retrieve events and both oracledb1.log and countfile1 are successfully updated.
However,the second wrapper script,call_dbipoll2.sh is successful only for the first run. For subsequent runs,I've noticed that countfile2 does not get updated with the latest timestamp(ie. the file returns to blank) and oracledb2.log returns to blank as well. Therefore,am unable to pull new events then. Unable to determine what went wrong here..
Here is sample of my second wrapper script that tries to retrieve events from the 2nd database:
CODE#!/bin/sh
#
export ORACLE_HOME=/ora-main/app/oracle/product/11.1.0.6/db
export ORACLE_SID=rmdev2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
#
[code]....
Server Administration :: Can Oracle Always Guarantee Data Consistent In Distributed Transaction
Mar 31, 2013I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:
Can Oracle always guarrantee the data consistent in a distributed transaction?
For example,there is a distributed transaction on node a,node b and nod c.node b and node c informed node a they were prepared,so node a committed,and inform node b and node c commit.then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?
If node c rollback the data on local node, consistent in this distributed transaction was failed ,yes?
Client Tools :: TOAD Not Finding Oracle 11g?
Jul 2, 2013I am running TOAD 10.6 on Windows 7 (64-bit). I have two 64-bit Oracle 11g clients installed (all on the same machine). The first is Instant Client (client_1); the second is the Runtime client (client_2). The installation of Instant Client was a mistake, since it doesn't have a Network / Admin folder to drop a tnsnames.ora file into. The Runtime client is my desired client, which has the aforementioned folder that I subsequently dropped the file into.
On starting TOAD, it says that "No valid Oracle Client found. Please note that Toad only supports 32 bit Oracle Client installations". (This is odd, since my colleague is running the same Oracle Runtime client as me and has TOAD 11.6, and yet his is working fine. But, this is aside the point.) When, I click on the ellipsis button next to the "Connecting Using" field, TOAD says that I don't have any Oracle homes installed. I checked my registry key (via regedit) under Computer / HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE. It looks like I have 2 Oracle homes (because of the 2 clients).
Also, I right-clicked on My Computer and brought up Properties, Advanced system settings, Environment Variables (on the Advanced tab). My PATH variable has both of my clients.
How to make my available clients (particularly, my client_2) show up in TOAD?
Here is what I have tried thus far:
-Alter the PATH variable to just have client_2. This didn't do anything.
-Try to uninstall client_1 and client_2 via:
a) There is a Universal Installer under Oracle Installation Products in my Runtime client. I do Deinstall Products, click on any of my Oracle homes, click on Remove, and a Warning window pops up saying to run a command at some location, but the location is truncated since I can't expand the window.
b) In my client_2, there is a deinstall Windows Batch File in the deinstall folder. When I run this, the command window sits there for a little bit, saying that some files were copied and that 1 directory was removed. It then closes on its own. This doesn't seem to do anything.
Forms :: Finding Out Port Number And Oracle ID For Compilation?
May 21, 2010Is there any way i could figure out what is the current port number used in an existing setup of oracle forms in a linux server.
Is there any specific oracle id i should use to compile oracle forms on server?
SQL & PL/SQL :: Finding Function In Oracle Which Removes All HTML Tags?
Dec 14, 2010is there any function in oracle which removes all HTML tags . our web pages uses rich text editor, when user enters text into editorand saves it, the database will have text with formatting ie all html tags. this works fine as long we display this text in web pages , but some time we have to provide reports to user just by running sql and ouptut in excel, in this case I want to strip out all html tags within the text .there is any function which can strip all HTML tags?
View 6 Replies View RelatedSQL & PL/SQL :: Set Transaction Use Rollback?
Aug 31, 2013Having PL/SQL procedure and calling sys dba procedure to alter rollback segment to online and after that to set the transaction to this rollback. The rollback altered to online but transaction still running on available rollback and not the one I was meant to. The user have executing grant on the sys dba procedure.
View 21 Replies View RelatedSQL & PL/SQL :: What Is Autonomous Transaction
Jul 28, 2010what is autonomous transaction
View 8 Replies View RelatedClient IP Of Previous Transaction
Mar 11, 2013Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?
Oracle server version I'm using is 11g.