Difference Between Coalesce And Defragmentation?
Sep 1, 2012What are all the differences between Coalesce and De-fragmentation? When i need to use Coalesce?
View 4 Replies
ADVERTISEMENT
Fragmentation / Defragmentation Of Space In Database
Jul 31, 2012Oracle DB version : 10.2.0.5.0
OS version HP-UX B.11.31 U ia64
DB size : 2TB
We have the above configuration for one of our oracle database. One of our DBA acts like he is only the person on this earth who is managing the process of fragmentation removal from the tablespaces in order to improve the performance and wastage of the space. He performs that task at weekends and takes one-off day extra. I am not sure how the fragmentation removal improves the performance and deallocates the space.
Is it compulsory to perform the rebuild process weekly in order to remove fragmented space from tablespaces? Do we have any other method to automatically re-organize objects occupying waste space?
Why the reuild of indexes using separate tablespace improves performance? is there any specifi reason for it?
COALESCE Or Shrinking Datafile?
Sep 6, 2010We have quarterly and yearly processes that deletes and updates data for millions of rows crossing different tables. To make room on the file system I would either like to remove empty datafiles (my preference) or coalesce TS to compact the data than remove the empty datafile.
Having said that, any query that can show me if a datafile has any data in it or a query that can show me whether a TS is a good candidate to be coalesced maybe a query with something displaying a percentage as to free and used extents and a YES/NO column whether to be coalesced or not.
Performance Tuning :: Oracle Coalesce Vs Index Rebuild?
Dec 30, 2010We have large tables 60-70 GB having 120 million records. We have to perform index rebuild frequently which takes significant time to complete and effects database performance too. how we can use index Coalesce? what are its benefits, coalesce results in performance gain?
View 10 Replies View RelatedPL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?
Oct 17, 2012Detail table will look like below:
Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....
Need the data like
Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)
10/10/2012 3 4 1 4 2 1 0 0
How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?
SQL & PL/SQL :: Get Max Difference Where Each Value Can Look At Only Next?
May 28, 2013How do I get the max difference where each value can look at only the next value(ordered by date asc) on any particular day
example first row on 20090902 is 501.25 (earliest date)
756.2 - 501.25
4735.83 - 501.25
35.83 - 501.25
[code]....
How To Get Date Difference
Jul 20, 2007How one can get date difference in days between two dates in Oracle reports ?
View 2 Replies View RelatedSQL & PL/SQL :: Difference Between Dates
Jan 23, 2013I am looking to subtract two columns and get the difference.
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL & PL/SQL :: ORA-00923 - Difference Between 9i And 10g
Aug 25, 2010I encountered this problem when we recently migrated to 10g. The table nametab has two columns roll(number datatype) and name(varchar2(10) datatype).Following are the queries used and I have deliberately not given space between the "select" clause and the "from" clause.
In 9i
SELECT name,1FROM nametab where rownum < 2
NAME 1
---------- ----------
Bharath 1
Same query in 10g
SELECT name,1FROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
Here, I have interchanged the select columns.
In 9i
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
In 10g
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
SQL & PL/SQL :: Difference Between Oracle 9i And 10g?
Apr 25, 2013what are difference in between oracle 9i and 10g regarding with Sql queries?
View 1 Replies View RelatedSQL & PL/SQL :: Difference Between Using MINUS And NOT IN
Sep 28, 2011I am looking for an explanation for having the following query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 MINUS (SELECT FK_SERIAL_NUMBER FROM TABLE2);
Which normally returns the values of SERIAL_NUMBER that are not passed to the child table TABLE2
and the query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 WHERE PK_SERIAL_NUMBER
NOT IN
(
SELECT FK_SERIAL_NUMBER FROM TABLE2
);
which returns 0 rows!
Difference Between V$sql And Dba_hist_sqlstat?
May 30, 20111. difference between v$sql and dba_hist_sqlstat?
2. as of i have understood, dba_hist_sqlstat gets refreshed from v$sql.
if i run this below query i get different outputs.. how?
by using v$sql table
WITH t AS
(SELECT force_matching_signature ,
SUM(executions_delta) executions,
COUNT(*) cnt ,
parsing_schema_name ,
MIN(MODILE1) MODULE1
[code].......
SQL & PL/SQL :: Difference Between Oracle 8i And 11g
Sep 22, 2011I listen that there is a big diff B/W indexing in oracle 8i and oracle 11g.
what way the indexing is different B/W 8i and 11g.
PL/SQL :: Difference Between 2 Dates
Sep 24, 2012for the below table
create table RM_TR_INVESTMENT
(
AS_ON_DT DATE not null,
EXP_ID NUMBER(10) not null,
BO_REF_ID VARCHAR2(30),
FO_REF_ID VARCHAR2(30),
BK_PRODUCT_ID VARCHAR2(20),
BK_INV_TYPE_ID VARCHAR2(20),
[code]....
I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?
PL/SQL :: Difference Between Oracle DB 11.2.0.1.0 And 11.2.0.3.0?
Dec 18, 2012When I use this sql: CREATE USER testuser IDENTIFIED BY "welcome1 " to create user in Oracle DB 11.2.0.1.0. I can login with testuser/welcome1, please notice the welcome1 is not end of black space in login.
But when I do the same thing in Oracle DB 11.2.0.3.0. I can't use this user login with any password. I tried welcome1, "welcome1 " but all failed.
Why there are this difference between 11.2.0.1.0 and 11.2.0.3.0, and since in 11.2.0.3.0 the create sql are success, what password is correct when login?
SQL & PL/SQL :: How To Get Difference From Hh24:mi
Aug 17, 2013I have two columns StartTime and EndTIme.
I have data like this
EndTime StartTime Diff(endtime-StartTime)
23:00 20:00 03:00
22:00 21:30 00:30
How can i get Diff(endtime-StartTime) from hh24:mi format
Difference Between Database Or SID Upgrade
Oct 19, 2012i want to know is there any difference between upgradation of SID and database. while upgrading my database from oracle 11.2.0.2 to 11.2.0.3 in DBUA it shows my SID is upgrading (ORCL) but i have few other databases ex (test and prod).
is it enough if we upgrade the SID or we must perform any other actions..
and
2nd QUERY :
i have 2 oracle homes in my server with different versions like (11.2.0.2 and 11.2.0.3)and i have few databases and there tablespaces. how to determine which database is created on which version and which database is upgraded from 11.2.0.2 to 11.2.0.3.
Difference Between ADD DISK And ADD VOLUME
May 27, 2013I have a question about the ADD VOLUME command, I can't understand the difference between ADD DISK and ADD VOLUME.What are the difference between them?When should I use each one?How can I control the stripping and mirroring (NORMAL and HIGH) adding VOLUMES in a DISKGROUP?Can I add a volume to a fail group?
View 1 Replies View RelatedDifference Between Oracle Version 10.2.0.3 And 10.2.03?
May 28, 2008difference between oracle version 10.2.0.3 and 10.2.03.I searched a lots of sites to get the details about these versions but unable to collect.
Oracle released both version or 10.2.0.3 and 10.2.03 are same versions treated by oracle.
Difference Between Number And String?
Oct 20, 2012I can't understand >>
1. Number 2 is less than number 100,
2. string '2' is greater than string '100'.
>> Numbers are ordered by numerical value; strings are ordered by alphabetical value. >>
SQL & PL/SQL :: Difference Between SCHEMA And ENTITY?
Dec 7, 2011What is the difference between a SCHEMA and an ENTITY?
View 5 Replies View RelatedSQL & PL/SQL :: Finding The Difference Between IN And EXIST?
Nov 22, 2011finding the difference between IN and EXIST.
View 15 Replies View RelatedRAC & Failsafe :: Difference Between RAC And Non-RAC Database
Sep 13, 2012Tell me the difference between Oracle RAC and Non RAC database including commands and architecture?
View 4 Replies View RelatedSQL & PL/SQL :: Find Difference In Two Schemas?
Feb 25, 2013I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.
View 2 Replies View RelatedSQL & PL/SQL :: Difference Between Procedure And Function
May 22, 2010I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.
both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?
SQL & PL/SQL :: How To Get Difference Between Two Rows Of Same Column
Aug 3, 2013Calculating the difference between the two rows of same columns? Example of my Scenario is as follows
I have a table as shown below, there are checks thats has been issued per week (to tell how its been issued per week ,if you see the week number ,there is a change in week no for period of 7 days i.e. what i have to show on my results tables based on week no and probably we may need to take into account for check no too.)
wekno ! checkno ! end date
------ ! ------ ! ------
1 ! 1 ! 1/7/2012
1 ! 2 ! 1/8/2012
1 ! 3 !1/9/2012
2 ! 1 ! 1/14/2012
2 ! 2 ! 1/15/2012
3 ! 1 ! 1/21/2012
result table and date range i picked up was 1/1/2012-1/31/2012
wekno ! checkno ! end date ! previous period ! Daysin period
------ ! ------ ! ------ ! ------------- ! ------------
1 ! 1 ! 1/7/2012 ! null (as previous period is 2011 and so prvs year i dont count) ! 7 this because we see change in wkno per 7 days
1 ! 2 ! 1/8/2012 !null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
1 ! 3 ! 1/9/2012 ! null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
2 ! 1 ! 1/14/2012 ! 1/7/2012 becoz this a second week as you see the change in wekno and therefore its previosu period is 1/7/2012 ! 7 above reason
2 ! 2 ! 1/15/2012 ! 1/7/2012 becoz this check falls on the same second week so previosu would be 1/7/2012 ! 7 above reason
3 ! 1 ! 1/21/2012 ! 1/14/2012 as a new week no starts this gonn be 1/4/2012 ! 7 above reason
SQL & PL/SQL :: Difference Between In Procedure And Trigger?
Jul 7, 2010what is difference between in procedure and trigger?
View 2 Replies View RelatedSQL & PL/SQL :: How To Calculate Time Difference
Aug 10, 2003I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.
View 4 Replies View RelatedSQL & PL/SQL :: Difference Between Sysdate And Current_date
Apr 15, 2010What is the difference between the values of sysdate and current_date ? On querying the database I got the results as sysdate = the date and time for the database server location and current_date = my local system date
If however, i change my local system date - it still shows the correct date.Myunderstanding was that current_date uses the dbtimezone.But..
SQL> select sysdate,current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
15-APR-2010 06:06:14 15-APR-2010 18:36:15
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
SQL>
SQL & PL/SQL :: Difference Between Dba_scheduler_jobs And User_jobs?
May 28, 2010when i crate a job using dbms_schedule.create_job , i am able to create it successfully and also i can see that job is running in dba_scheduler_jobs table.how can i see the same job in user_jobs as that table also shows the jobs that are running..
View 6 Replies View Related