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
ADVERTISEMENT
Find 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]........
Sort 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]........
SQL & PL/SQL :: Query That Will Give List Of Views Using Three Tables?
Aug 16, 2011I 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.
Backup & Recovery :: Selected Data From Tables Of Schema
Mar 7, 2012I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.
View 11 Replies View RelatedServer Utilities :: Exporting Database Schema / Tables Without Data
Sep 22, 2010I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).
For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?
Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.
PL/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
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 RelatedServer 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 RelatedSQL & PL/SQL :: List All Tables Connected To Each Other With Constraints And List All Together
Apr 22, 2013I 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 RelatedSQL & PL/SQL :: Find Logic To Query Data Based On A Condition
Aug 18, 2013find the logic to query data based on a condition like..I am having a table dummy
USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
How To Find Out The List Of Patches Applied Without Using Inventory
Jul 23, 2012I have been asked this question once.
How will you find out the list of patches applied to Oracle Database Home without using commands like opatch lsinventory -detal etc...
I think registry$history is a view from where we can find out the list of patches applied.
But I think it will not include all the bug fixes,stand alone or one-off patches.It will mainly list out the CPU patches applied(correct me if I am wrong).
SQL & PL/SQL :: How To List All Tables Containing Column Name
Aug 4, 2011How 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 RelatedFind Schema Stats Lock Or Unlock
Apr 20, 2013How to find schema stats locked or unlocked?
View 3 Replies View RelatedSQL & PL/SQL :: Create Tables From Different Schema?
Mar 18, 2012I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.
View 3 Replies View RelatedPL/SQL :: Trigger From Other Tables Of Same Schema?
Jan 14, 2013 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
i'm trying since some hours to get information from inside a trigger from another table of the same schema.My trigger table is PSE_BKB.NUM_PHANTOM_BP
First i tried simple solution ...
CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
[code]...
The last version i tried was with a cursor definition inside the trigger as in the code-block below.For debugging purposes i've inserted a RAISE_APPLICATION_ERROR in the inner loop - see below. The variable v_obj_key is never set, like in all other variations i tried - i alway see the predefined 'gugus' from the declare section.
It seems oracle cannot read from other tables at this point. The :old.phantom_key is set (in this simple example there should come one obj_key back).
trigger:
CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
[code]...
Security :: Find A List Of Roles And Privileges In Database?
Feb 24, 2011How can I list all the roles and privileges of roles in Oracle? Can I get the details of the activities they perform?
View 12 Replies View RelatedNetworking And Gateways :: Find List Of Database Links?
Jul 2, 2011There is a user account called 'BALA' in my database.I want to find out the database links that uses this 'BALA' account.
View 2 Replies View RelatedPL/SQL :: Find A Check List To Use Against PROD / DEV / TEST Environments?
Jan 21, 2013outlining 'key' differences between databases / configuration / hardware that can potentially make a difference to what results you get when running sql / pl/sql - I am trying to find a check list to use against PROD / DEV / TEST environments.
View 2 Replies View RelatedGetting List Of Populate Tables In A Database?
Feb 9, 2012The 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 RelatedSQL & PL/SQL :: How To Get Distinct List Of Clients From 2 Tables
Nov 8, 2010I 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
SQL & PL/SQL :: Retrieve List Of Tables With Same Name From Two Schemas
Sep 16, 2013I need to retrieve the list of tables with same name from 2 different schemas.
View 10 Replies View RelatedSQL & 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.
PL/SQL :: List Of Unused Tables And Procedures
Aug 28, 2012I 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.
PL/SQL :: How To Join Tables And Get Ppl Not In The List From Other Table
Nov 2, 2013I 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_';
Performance Tuning :: How To Find All Indexes Scripts In Particular Schema
Aug 16, 2012getting all indexes script in particular schema.
View 4 Replies View RelatedCreate One Trigger Before DML On All Schema Tables
Nov 28, 2011We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install
I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).
So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.
Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)
SQL & PL/SQL :: Get Row Counts Of Tables In Schema Without Using Num_rows
Aug 9, 2010I am trying to get a row count(*) for all the tables in my schema. The NUM_ROWS column in DBA_TABLES is not appropriate in this case because they are as good as the last analyze. So I need to get real time counts.
I tried the following code but I can't seem to catch my error.
DECLARE
l_sql varchar2(150);
cursor tablelist is
select table_name from dba_tables where owner = 'ME';
[Code]....
My expected results are :
TABLE_NAME ROW_COUNT
---------- ----------
Client Tools :: List Of Tables In PL/SQL Developer
Feb 4, 2010I 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.
Security :: Possible To Get List Of Tables On Which Audits Enabled
Apr 19, 2013Is it possible to get a list of tables in a schema on which Audits are enabled.
View 2 Replies View Related