How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.
View 3 RepliesI need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies View RelatedI have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index
Ex. create index test1 on customer (ID , Status )
But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?
I have three tables,let's say
table stores
sid | store_name
1 | one
2 | two
3 | three
table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree
table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2
and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)
is there a way to find out which unused columns in oracle?
View 8 Replies View Relatedquery to find out common data from 2 columns in two different tables??
View 6 Replies View RelatedGiven a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies View RelatedI need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788
How to find non-indexed queries and columns that require indexes?
View 3 Replies View RelatedI have two columns in database
INPUTDATE DDMMYYYY
and
OUTPUTDATE YYYYMMDD
I want to compare both the columns because of format of columns i am getting problem to compare.
I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.
Before Swaping:
Columns Values
TABLE1.IDENTIFIER MRN123
TABLE1.MEDICARENO CHI1234567
TABLE2.MRN MRN123
TABLE3.MRN MRN123
OTHERTABLE.MRN MRN123
After Swapping:
Columns Values
TABLE1.IDENTIFIER CHI1234567
TABLE1.MEDICARENO MRN123
TABLE2.MRN CHI1234567
TABLE3.MRN CHI1234567
OTHERTABLE.MRN CHI1234567
I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.
View 11 Replies View RelatedAs we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
View 1 Replies View Relatedi want a query/function/procedure to check recently updated columns/tables in a database...
View -1 Replies View RelatedI am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
We have a 2 node RAC runing on 11.2.0.2 and last night the database was totally un-responsive. when i checked the ADDM I noticed the following:
Waiting for event "cursor: pin S wait on X" in wait class "Concurrency" accounted for 98% of the database time spent in processing the SQL statement with SQL_ID "4b2epo0eaqol9".I am wondering what option do i have here? i am looking to do the following:
1) find the root cause why the database was un-responsive
2) ADDM is listing the query, what options do we have further?
How do you find the youngest user in a certain table
select a.illness_desc
from uma.pc_illness a, uma.pc_patient b, uma.pc_diagnosis c
where b.patient_no = c.patient_no and c.illness_code = a.illness_code
and b.age = '21';
i currently have this (it also needs to display their illness which i have done) but i can do only if i cheat and see what the youngest user is, so instead of b.age = 21 i need something that will search the database and find that youngest age
we are using SQL+
I need the column name and table name which is having the particular value in the whole database.
E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.
I need to find all the tables present in DB or Schema.
View 3 Replies View Relatedif i have data like this
case 1
code start month end month
winter march march
summer april january
spring feburary feburary
case 2
winter January January
summer february september
spring october december
and if i am trying to enter again start month and end month . i should validate if the month i am giving is already present in the table for any of the season
i want a do the validate with a query so that i will not enter duplicates.
There is one DBMSJOB i created schedule to run every day at 22:00 hrs. The jobname is IT_TO_DUM_LOADING and the job type is Stored procedure.
I queried all_source and found stored procedure does not exist in database.
SQL> select text from all_source
2 where type = 'PROCEDURE'
3 and name ='IT_TO_DUM_LOADING';
no rows selected
But i could see the job details in dba_scheduler_jobs view. I cannot use WHERE clause in dba_scheduler_jobs to restrict and find only the job row as i receive time zone error. How it is possible to view the job in dba_scheduler job view whereas i could not find the stored procedure of its in all_source?
query to find the current user accessing the database
View 5 Replies View RelatedAs a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies View RelatedHow can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies View RelatedI am trying to patch an Oracle 11g database. I can see from the logs that is was patched after it was created by a consultant but I cannot get Opatch to work.
C:\app\Oracle\product\11.2.0\dbhome_1>java -version
java version "1.5.0_17"
Java™ 2 Runtime Environment, Standard Edition (build 1.5.0_17-b03)
Java HotSpot™ Client VM (build 1.5.0_17-b03, mixed mode)
C:\app\Oracle\product\11.2.0\dbhome_1>opatch>set ORACLE_HOME=C:\app\Oracle\product\11.2.0\dbhome_1
C:\app\Oracle\product\11.2.0\dbhome_1>opatch lsinventory -all
ORACLE_HOME is NOT set at OPatch invocation
The Oracle Home C:\app\Oracle\product is not OUI based home. Please give proper Oracle Home.
OPatch returns with error code = 1
OPatch failed with error code = 1
Java is set in the environment path but the Oracle home seems to be the issue. I checked the LOC parameter in the Oracle/Inventory folder and it is set correctly to C:\app\Oracle\product\11.2.0\dbhome_1.
Is there a way to find out the user access the database?
View 1 Replies View Relatedfind the Execution time frequency of the database jobs?i need the Execution time like this..
Job A is Running on every monday 10:30 AM OR
Job A is Running on every MONTH OR
Job A is Running on every DAY 10:30 AM
I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?