SQL & PL/SQL :: List Records Being Deleted From All The Tables

Jan 10, 2011

I 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.

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Deleted All Records From Table - How To Get Back

Nov 8, 2010

I 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 Related

Restore Deleted Records From A Table

Jul 31, 2012

Somebody 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

View 6 Replies View Related

SQL & PL/SQL :: Find Out Tables That Are Frequently Getting Deleted?

Mar 25, 2012

How to find out the tables that are frequently getting deleted?

View 2 Replies View Related

Forms :: FRM 40102 Records Must Be Inserted Or Deleted First?

Jul 26, 2011

How to avoid this error. FRM 40102 Records must be inserted or deleted first

i have used next_record.If i remove this it is working.but i need to use next_record built in.

View 8 Replies View Related

SQL & PL/SQL :: List All Tables Connected To Each Other With Constraints And List All Together

Apr 22, 2013

I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.

View 2 Replies View Related

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:

The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.

Query for anyone interested:

Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........

I have a separate query which returns a list of support resources.

select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'

What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.

(the res.unique_name field in the second query can be linked to the same in the first query)

Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.

View 18 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

SQL & PL/SQL :: Count Of Records In Item List

Apr 17, 2012

One of the tables is having

SQL> select count(1) from item_list;

COUNT(1)
----------
14356529

To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%

I tried finding the fragmentation using the below query.

select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc

TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306

View 15 Replies View Related

SQL & PL/SQL :: Inserting New Record In Between List Of Records

May 2, 2012

I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?

Let's consider an example with 5 records:

Table data:

CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/

[Code]...

So when we check the table, we get:

SELECT * FROM enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

What I would like to have when I run the select query:

SELECT * from enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.

View 6 Replies View Related

SQL & PL/SQL :: How To List All Tables Containing Column Name

Aug 4, 2011

How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'

View 8 Replies View Related

SQL & PL/SQL :: Generate List Of Records Within Range (two Values)?

Mar 15, 2012

I am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:

Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.

I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.

How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?

View 3 Replies View Related

Getting List Of Populate Tables In A Database?

Feb 9, 2012

The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Distinct List Of Clients From 2 Tables

Nov 8, 2010

I have 2 tables which contain 1000's of cash and stock transactions and I need to somehow get a list of the distinct clients

select distinct client_id
from pf_cash_txns pct, pf_stock_txns pst

Quote:ORA-00918: column ambiguously defined

View 5 Replies View Related

SQL & PL/SQL :: Retrieve List Of Tables With Same Name From Two Schemas

Sep 16, 2013

I need to retrieve the list of tables with same name from 2 different schemas.

View 10 Replies View Related

PL/SQL :: List Of Unused Tables And Procedures

Aug 28, 2012

I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?

Give sql query to find the list of unused tables and procedures.

View 6 Replies View Related

PL/SQL :: How To Join Tables And Get Ppl Not In The List From Other Table

Nov 2, 2013

I have 2 tables Table a(girlscoutid, item, quarter)Table b(girlscoutid, fname, lname) I want to get the names of ppl who did not sell any item/s for the

4th quarter  
girlscoutid item quarter7771
drinks 4QS9000
tickets 4QW7771
cookies 2QS5085books 3QF3010tickets3QW5085
cookies1QF lscoutid
fnamelname7771 jenn richochet9000 laura wilkins5085sally mae3010dora mckenzie 

This is what I have, however, I get duplicates and  also 7771 shows up on the list even though she sold an item on the 4Q.

  SELECT fname, lname, a.girlscoutid
FROM a,b
WHERE a.girlscoutid = b.girlscoutidAND term NOT LIKE '4Q_';

View 4 Replies View Related

Forms :: Number Of Records Displayed In List Item Is More Than 1

Dec 28, 2010

Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.

View 12 Replies View Related

SQL & PL/SQL :: Query To Retrieve List Of Order_id Which Has No Records In Database

Jul 20, 2010

Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.

View 12 Replies View Related

Client Tools :: List Of Tables In PL/SQL Developer

Feb 4, 2010

I can do any sql commands against the schema I want (PSUSYS) with my username(SCHWAN) in the sql window, but when I want to export a table(created with a different schema) no tables are listed in the export window. This does work for my coworkers with the same access.

At one time this used to work so I'm wondering if it is a parameter that I inadvertently changed.

View 5 Replies View Related

SQL & PL/SQL :: Query That Will Give List Of Views Using Three Tables?

Aug 16, 2011

I wanted a query that will give me the list of views using three tables

when i fire

select * from dba_views where text like '%SELECT%';

it is giving me error as expected number got long.

View 2 Replies View Related

Security :: Possible To Get List Of Tables On Which Audits Enabled

Apr 19, 2013

Is it possible to get a list of tables in a schema on which Audits are enabled.

View 2 Replies View Related

PL/SQL :: Variable To Pass A List Of Names Between 2 Tables

Jun 4, 2013

I am trying to pass a list of names from one table as a variable to another table and trying to find the Highest level they exist at in the hierarchy. I am having some troubles with the variable and need some guidence and also if this should be a procedure or not.

this is what i have now :

Variable man_name Varchar;
exec :man_name = (Select full_name from direct_manager_report)
UPDATE direct_manager_report
Set Manager_level_number =

[Code]....

Let me know what modifications have to be done for this the variable to get the name from table 1 and find the manager level from table 2 and populate it back in table 1.

View 2 Replies View Related

Find List Of Tables Inserted / Updated In Last 6 Month

Oct 7, 2012

I 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]........

View 1 Replies View Related

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related

SQL & PL/SQL :: List All Tables In Database With Particular Record Values Combination

Jul 1, 2013

I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.

empidenamejob
7369, 'SMITH', 'CLERK'

I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?

View 3 Replies View Related

ILM Assistant :: Unable To Get List Of All The Tables Of Oracle EBS R12 Database?

May 13, 2013

I am trying to attach lifecycle definitions to tables of Oracle EBS R12 database, using the "Refresh table list" option of ILM assitant.

Though its showing the refresh job running to completion, but its not displaying the list all of tables.

Tried it with narrowing the search for a single schema but no luck.

how to get the list of all tables or at least list of tables belonging to a schema in Oracle EBS R12.

View 1 Replies View Related

Enterprise Manager :: List Of Tablespaces And What Tables And Indexes Are In Those Tablsespaces?

Oct 21, 2011

Whats the easiest way I can see a list of tablespaces and what tables and indexes are in those tablsespaces? Can this be done through OEM?

View 3 Replies View Related

SQL & PL/SQL :: How To Insert 100 Records Into Different Tables

Apr 28, 2011

i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.

View 22 Replies View Related







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