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.
we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared
Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103
[code]...
Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.
Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.
Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?
Going through my first attempt ever to connect to a Oracle database with a .Net application. Using Visual Studio 2012, I have downloaded and installed the latest version of odp.net, actually both the 32 and 64bit versions.
Application is targeting the 4.5 Framework, Latest version of Entity Framework installed (but not important I think at this point) Added a reference in my project to the Oracle Data Provider ( tried with both 2.0 and 4.0 )
Created a connection string with EZ Connect, stored in web.config file. I have tried 2 different projects to use EF and one without. Trying to create a edmx or using a datasource adapter brings up the connection wizard or prompts to use the existing one, but no matter what I try, there are no tables, views or procedures listed.
From Server Explorer, I can open the Data Connections and verify that the connection to the Oracle database is Open. During the Connection Wizard, there were no errors, and all the databases available were listed. Yet, connecting to and opening the connection to this specific database or anyone for that matter no tables, views or procedures are listed.
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 RelatedHow 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 RelatedThe 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 RelatedI 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
I need to retrieve the list of tables with same name from 2 different schemas.
View 10 Replies View RelatedI 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.
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_';
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.
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.
Is it possible to get a list of tables in a schema on which Audits are enabled.
View 2 Replies View RelatedI 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.
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]........
I want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedI 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 ?
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.
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 RelatedI have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
A way to identify variables declared in a procedure that are not being used? I had thought plsql_warnings might do the trick but it does not.
I have inherited a lot of code that was ill conceived and unfortunately have started to notice a trend.
I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.
marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.
I have a problem...
I created a tablespace called my_ts:
CODECREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
ALTER DATABASE DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;
Its was sucessfully created and my_ts.dbf file has 5MB
charging with data...
CODEcreate table big_table tablespace my_ts as select * from dba_objects;
select * from big_table;
begin
for i in 1..10 loop
insert into big_table select * from dba_objects;
end loop;
end;
Now the my_ts.dbf file has 90MB
Now I want drop this table:
CODEdrop table big_table purge;
And my tablespace file still has 90MB.
I already tried to restart the database but doesn't works...
how can I reduce the size to the original (5MB)?
I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
LOC NO
DNAME NO
DEPTNO NO
orcl> alter table dept set unused column loc;
Table altered.
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
SYS_C00003_13071316:19:02$ YES
DNAME NO
DEPTNO NO
orcl> select "SYS_C00003_13071316:19:02$" from dept;
select "SYS_C00003_13071316:19:02$" from dept
*
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept rename column "SYS_C00003_13071316:19:02$"
2 to loc;
alter table dept rename column "SYS_C00003_13071316:19:02$"
*
ERROR at line 1:ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept modify "SYS_C00003_13071316:19:02$"
2 visible;
alter table dept modify "SYS_C00003_13071316:19:02$"
*
ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies View Relatedwhat happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.
My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?
Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.
is there a way to find out which unused columns in oracle?
View 8 Replies View RelatedI am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.