SQL & PL/SQL :: Find Out Tables That Are Frequently Getting Deleted?
Mar 25, 2012How to find out the tables that are frequently getting deleted?
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: List Records Being Deleted From All The Tables
Jan 10, 2011I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
Enterprise Manager :: How Frequently Alert Metrics Are Collected / Refreshed
Oct 5, 2010I have created some views in one SCHEMA and made it invalid by dropping it's base table
1) Thru some queries, on SQL, I checked it shows there are invalid objects.
2) But even though , I refreshed DBCONSOLE Home Page it doesn't show the invalid objects.
3) How frequently ALERT METRICS are collected/refreshed.
Networking And Gateways :: ORA-3136 Frequently In Oracle Db Alert Log File
Jan 2, 2011I am facing below error so frequently in alert log file Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.6.0 - Production
Time: 03-JAN-2011 02:00:00
[code]....
SQL & PL/SQL :: ORA-01000 Maximum Open Cursors Exceeded Frequently While Running Application In Java
Jun 23, 2011I am working on SQL,PLSQL and my application is in Java. I have 2 schemas in same database.
In one schema, the DML operations are very slow while in other schema is fine.
Also I am getting an error ORA-01000 maximum open cursors exceeded frequently while running application in Java.
SQL & PL/SQL :: Find All The Tables Present In Database?
Jun 8, 2010I need to find all the tables present in DB or Schema.
View 3 Replies View RelatedSQL & PL/SQL :: Find Locked Tables In Hierarchy
Feb 1, 2012Is it possible to find the locked objects in hierarchical order. Consider the below example
--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......
Is it possible to get all locked table details in hierarchical order?
SQL & PL/SQL :: How To Find Tables Which Does Not Have Specific Columns
Jan 11, 2013I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies View RelatedHow To Find Over Fragmented Tables / Indexes
Jun 19, 2012i am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?
View 4 Replies View RelatedPL/SQL :: Find The Duplicate Data In Tables 11g
Feb 19, 2013i want to find the duplicate data in my tables i have the below structure
SQL> desc x;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_CODE NUMBER(20)
STREAMNUMBER VARCHAR2(50)
SERVICEKEY VARCHAR2(50)
CALLINGPARTYNUMBER VARCHAR2(50)
CALLEDPARTYNUMBER VARCHAR2(50)
CHARGEPARTYINDICATOR VARCHAR2(50)
[Code] ......
i found the below query it's right
SELECT * x
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM x
GROUP by FILE_CODE,..........................,SEQ_NO ); all the column
SQL & PL/SQL :: Joins - Find Missing Records Between Two Tables?
Mar 7, 2010I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
PL/SQL :: Find Common Data In 2 Columns In Two Different Tables
Oct 22, 2012query to find out common data from 2 columns in two different tables??
View 6 Replies View RelatedFind List Of Tables Inserted / Updated In Last 6 Month
Oct 7, 2012I tried to find out recently updated table list. I couldn't find anything.
CODE>> JUST SAMPLE TRANSACTIONS >>
SQL> conn sam
Enter password:
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$y1TXhcjGApvgQAB/AQBOpA==$0 TABLE
TAB1 TABLE
[code]........
SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?
Feb 3, 2012I want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedSort Through Schema Tables To Find If Particular Vendor Number Exist
Nov 14, 2006I would like to be able to sort through a schema's tables to find if particular vendor numbers exist. The decode cross tab works fine, except that it needs static data for each table. How can I code a PL/SQL to populate the table_name and give a count for each row? See example below:
col table_name format a15
set numwidth 10
set lines 1000
set pages 50
select b1.table_name,
count(case when a.vendor='86444' then 1 else null end) "86444",
count(case when a.vendor='86445' then 1 else null end) "86445",
count(case when a.vendor='86469' then 1 else null end) "86469",
count(case when a.vendor='86470' then 1 else null end) "86470",
[code]........
Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day
May 26, 2011We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies View RelatedPerformance Tuning :: How To Find Tables In Database On Which High DMLs Firing
Feb 4, 2011How to find the tables in the database on which high DMLs are firing.
View 5 Replies View RelatedSQL & PL/SQL :: Multiple Values Of RULES Tables To Find Records Containing A String Format
Sep 4, 2013Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
Enterprise Manager :: Querying Grid Control Tables To Find Size And Name Of Database?
Sep 25, 2012query to get the name and size of all the databases associated to grid..
I need to find this to do capacity planning in our environment.
SQL & PL/SQL :: Find All Column Names In All 20 Tables That Have Same Names?
Jul 7, 2010I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
Rows Are Not Getting Deleted Using Purge
Nov 8, 2012declare
min_as_of_dt date;
max_as_of_dt date := to_date('30-SEP-2012', 'DD-MON-YYYY');
begin
DBMS_OUTPUT.ENABLE(10000000);
[code]......
Problem: rows are not getting deleted after running this script.
Oracle_home / Bin Directory Deleted
Nov 4, 2011I have a major problem my oracle_home/bin directory was delete mistakenly by on of our user. Because i cannot use all oracle utitilessqlplus,dbca,netca,netmgr. how do i the cover this problem?
View 2 Replies View RelatedSQL & PL/SQL :: How To Take Backup Of Deleted Statement
Mar 27, 2012i want to insert values in another table whatever i deleted. i don't want whole information like backup, i just want deleted information only. whenever any 1 will try to delete any information on my database ,easily i would get to know what,when he has deleted .
View 14 Replies View RelatedSQL & PL/SQL :: Retrieve Deleted Rows
Jun 13, 2013how to retrieve deleted rows in oracle?
View 7 Replies View RelatedHow To Know Exact Time Of Row Modified And Deleted
Jul 16, 20101)How can i know that in a table when i modified a row or deleted a row and which row i inserted when i want to know the particular time
can it is possiable if possiable then tell me how.
2)Is there any difference between 9i merge and 10g merge command ?
SQL & PL/SQL :: Deleted All Records From Table - How To Get Back
Nov 8, 2010I have deleted all the records from the table.And I have committed.Now I want to get all the records back.
View 16 Replies View RelatedSQL & PL/SQL :: Deleted Table In Oracle 11g - How To Restore
Mar 11, 2013I deleted a table in oracle sql 11g about two weeks ago and commited. Didn't realise that I would need it later on and now I needed. is there a way a can get reverse this.
View 13 Replies View RelatedPossible To Recover A Deleted Column Of A Table
Mar 18, 2011I am new to oracle database. I wanted to know if it is possible to recover a deleted column of a table. If yes, how can it be done? I tried flashback but it seems that flashback doesn't support recovery of deleted column.
View 2 Replies View RelatedRestore Deleted Records From A Table
Jul 31, 2012Somebody deleted records from 10,12 tables in one of the schema. I found in one of the forum that table can be restored also if it exists in the recycle bin. I checked by querying:
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
> select type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin WHERE TYPE='TABLE' and droptime like '%2012-07-31%';
PHONE TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
- -
- - I created a test table and dropped it and restored from recycle bin using following flashback table test query....but in my case tables are not dropped...these tables are shoing 0 records. How can I recover only records from recycle bin tables?
SQL> flashback table test to before drop;
Flashback complete.
DB=10.2.0.5 on RHL
Reinsert Deleted Record By Using Same Trigger
Jan 24, 2013I have a trigger which monitors the deletion activity on the table. But I would like to re-insert those records to the in the table using the same trigger.
View 1 Replies View Related