Calculating Totalspace Consumed By Datafiles
Sep 20, 2012
11.2.0.2 on Solaris..I have such a large post on a very basic space calculation.
We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,
When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files
When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.
SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
------------------------- ------------------ -------------------- ---
WLMCS_DATA 32767.9844 32766.9375 YES
WLMCS_DATA 32767.9844 31615.875 YES
WLMCS_DATA 32767.9844 16419 YES
WLMCS_OAT_DATA 32767.9844 32766.9375 YES
[code]....
11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?
Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.
Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?
-- Not including datafile names for better readability.
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024
------------------------- ------------------ --------------------
WLMCS_DATA 32767.9844 32766.9375
WLMCS_DATA 32767.9844 31615.875
WLMCS_DATA 32767.9844 16419
WLMCS_OAT_DATA 32767.9844 32766.9375
[code]....
View 6 Replies
ADVERTISEMENT
Jun 24, 2013
if we update partition key, internally row movement is done as if you had in fact deleted the row and reinserted it. It will update every single index on this table, and delete the old entry and insert a new one. It will do the physical work of a DELETE plus an INSERT. However, it is considered an update by Oracle even though it physically deletes and inserts the row¿therefore, it won't cause INSERT and DELETE triggers to fire, just the UPDATE triggers. Additionally, child tables that might prevent a DELETE due to a foreign key constraint won¿t. You do have to be prepared, however, for the extra work that will be performed; it is much more expensive than a normal UPDATE.
CREATE TABLE MYTEST1
(
MYID NUMBER(10) PRIMARY KEY,
MYFLAG VARCHAR2(1)
) TABLESPACE SIMANG_D
PARTITION BY LIST (MYFLAG)
(
PARTITION P1 VALUES ('A') tablespace DJB,
PARTITION P2 VALUES ('B') tablespace users
);
[code]....
In above I inserted few rows into table and those belong to partition P1 and Tablespace DJB and then I updated partition key from A to B which resulted row movement from partition P1 to P2 which belong to tablespace users. Before updation data has consumed some space in tablespace DJB and after update those records consumed some space in tablespace USERS. But in this scenario I checked that space consumed in DJB is not released. Is there any way to get this space released ?
View 7 Replies
View Related
Jun 21, 2010
I have a one more query
col1 col2
3-18083017013-Standard
3-18083225012-Significant
3-18082775913-Standard
3-18082426912-Significant
3-18082097112-Significant
3-18081539512-Significant
3-18081946712-Significant
3-18080523612-Significant
3-18076873112-Significant
3-18076872712-Significant
3-18080522412-Significant
3-18080064612-Significant
3-18070899912-Significant
3-18081155713-Standard
3-18071160013-Standard
3-18077564213-Standard
3-18079220312-Significant
3-18073201313-Standard
note:
3-Standard 240 min
2-Significant 120 min
1) i need to calculate the percentage value.
general form is :
total number of col1_values(count) in 120 min/count(col1) *100
conditions to calculate percentage:
1)need to ca
View 16 Replies
View Related
Jul 15, 2011
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp
(Sal in number, Commission in number)
return number
as
[code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
View 9 Replies
View Related
Jul 10, 2012
10g
I have a table that mirrors remote table. Time info on the source table is in milliseconds (lowest value is 1338699905613)
I'd like to have the target table with DATE column as an addition to the source table columns.
I'm wondering what would be the way to calculate DATE value on insert?
The calculation should be from UTC (in milliseconds) into DATE.
The target table is :
test (ID NUMBER(19,0), STARTTIME NUMBER(19,0), RECORD_DATE DATE);
and data comes with insert from source table (mirror in remote db)
insert test (ID, STARTTIME) select id, STARTTIME from test_o
View 5 Replies
View Related
Jul 12, 2011
How can I calculate (by SQL) outstanding stock on a First In First Out basis per month?
Table A
Month Oustanding Stock Sales Expired Stock
January 200 0 0
February 100 100 0
March 100 0 0
April 50 150 0
June 50 0 100
July 50 0 0
Desired Results
Month Outstanding Stock
January 0
February 0
March 50
April 50
June 50
July 50
View 5 Replies
View Related
Aug 15, 2011
I have a field in Customers table called shipeddate....
I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...
But the business hrs of the store room are from 8am-5pm..
So when a shipped date is 4pm on MOnday
and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..
How can i achieve this...
View 2 Replies
View Related
May 8, 2010
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,
[Code].....
View 6 Replies
View Related
Nov 22, 2012
formulating sql query
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R
123 28-06-2012 21:36 Intransit Shipment
123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36
123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
Sample data and query I tried
with sampl_rownum_reset as
(select '123' inventory_item_id,
to_date ('28-Jun-2012 9:36:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
transaction_costed_date,
to_date ('28-Jun-2012 9:35:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
[Code]....
View 2 Replies
View Related
Nov 22, 2010
A function should accept two parameters: from_date and to_date which returns no.of Saturdays and Sundays between these dates and also show the dates of those weekends.
View 1 Replies
View Related
Sep 16, 2011
I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data
CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )
[code]....
View 11 Replies
View Related
Jan 2, 2013
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Example Table
TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000--11--UP--9801
02.01.13 12:44:03,236000000--12--DOWN--97346
02.01.13 14:05:03,845000000--11--DOWN--92485
[Code]....
View 16 Replies
View Related
Oct 17, 2012
[URL] ...... Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
I have the following table
CREATE TABLE "ALLOCATEASSOCIATES"
( "PROJID" VARCHAR2(30) NOT NULL ENABLE,
"ASSOCIATEID" NUMBER(*,0) NOT NULL ENABLE,
"ALLOCATIONSTARTDATE" DATE,
"ALLOCATIONPERCENT" NUMBER(*,0),
[code]...
Given that
1. An associate must be allocated to at-least and a maximum of 100% at any given point of time
2. User selects 2 dates between which inconsistency of allocation needs to be displayed
If the end user selects 1st Apr 2012 and July 31st 2012 between which reports needs to be generated, am looking for the following output
ASSOCIATE_ID FROM_DATE TO_DATE ALLOCATION_INCONSISTENCY
2 01-APR-12 15-APR-12 75
2 16-APR-12 15-JUN-12 25
2 16-JUN-12 30-JUN-12 50
The Allocation_Inconsistency denotes that the associate has a deficit of allocation between the 2 dates. The associate with ID 2 has a deficit of 75% of allocation from 1st Apr 2012 till 15th Apr 2012. Similarly 25% deficit between 16th Apr 2012 and 15th June 2012 and so on so forth. However, there is no allocation deficit for the month of July as he is allocated 100% for this month and hence is not appearing in the expected output.
View 5 Replies
View Related
Sep 2, 2011
I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4
How can I do this?
View 12 Replies
View Related
Dec 15, 2011
I have a tablespace PLUS_IX with 5 datafiles PLUS_IX_01, etc.
PLUS_IX_04 is 85% full, up from 78% last month
PLUS_IX_05 is 22% full.
PLUS_IX_01 to _03 are 73% full.
Should I be concerned that PLUS_IX_04 is getting full? If so, what actions are recommended?
ALTER INDEX ..... REBUILD has been run on all the indices.
View 1 Replies
View Related
Oct 31, 2012
I read this article:
[URL]......
Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?
View 3 Replies
View Related
Jul 31, 2012
Basically , we have a large index tablespace , whose data files already spans multiple mount points , and has been extended with a new data file on a wrong mount point by mistake , Can we relocate this last datafile extension , to a different mount point while the DB is online ? Is there any special consideration for index datafiles (If such classification is correct ) or is it just like any other Oracle non-system datafile ?
What will be the effect on the normal data tables which are using this index if they are being accessed during the exercise? This is a 2 node Oracle 9i 9.2.0.8.0 RAC on Veritas Cluster .
View 3 Replies
View Related
Jul 9, 2012
How to move the datafile from e-drive to d-drive in oracle 11g?
View 7 Replies
View Related
Oct 15, 2012
I am looking for a way to monitor the creation of new datafiles / tempfiles.Let's say I am creating a new datafile of 8GB in size, and it takes a few minutes to create.
I have looked at v$session_longops, and several other system views, but cannot find any that indicate how the file creation is progressing.
I want to be able to display something simple, such as "72% complete".
View 1 Replies
View Related
Oct 29, 2008
i want to delete one datafile from tablespace witch contain 2 datafiles
i did
ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;
and delete the file physiquly but i have already my datafile witch i have delete in my tablespace
View 14 Replies
View Related
Jul 12, 2010
Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:
"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"
The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.
Do I have to do it online or offline?
View 1 Replies
View Related
Jun 29, 2011
Can we restore a database if we have only the datafiles available? The database has been dropped but we have just the datafiles with NO system, sysaux,users and undo datafiles that were saved after taking the database offline and then dropping the database. I know this is strange but if possible, I want to see if we can retrieve some of the data from those datafiles to another database. BTW, There is no backup available for this database except the dbf files on OS.
View 3 Replies
View Related
Dec 20, 2011
Can we add a datafile mapped to a shared network drive in windows server.
View 1 Replies
View Related
Jan 20, 2011
when i install oraclexe on my system..when i want to connect the oraclexe then it give error
D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleXETNSListener 'net' is not recognized as an internal or external command, operable program or batch file.
D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleServiceXE 'net' is not recognized as an internal or external command, operable program or batch file.
D:oraclexeapporacleproduct10.2.0serverBIN>c
when i check this path D:oraclexeoradataXE then i check datafiles not generated in this folder
View 2 Replies
View Related
Oct 26, 2010
How to distribute the datafiles among various disks of a server, so that I/O operations are improved.
I mean is there any area we need to focus while distributing the disk like:
1)Particular types of datafiles should be placed in one disk.
2)Temp/Undo/Rollback tablespaces in another disk.
3)Indexes in another disk
View 4 Replies
View Related
Nov 25, 2010
Just noticed MR(media recovery lock ) in our database,further check showd all datafiles had this lock since our database was shutdown abortly but was startup gracefully.
View 2 Replies
View Related
Nov 17, 2011
What is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.
I.
1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.
II.
1.Stop application.
2.CONNECT AS SYSDBA
3.SHUTDOWN NORMAL or (IMMEDIATE)?
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.
View 1 Replies
View Related
Jan 21, 2013
I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.
View 6 Replies
View Related
Feb 3, 2013
i encoutered lately on such statement:
Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....
why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?
View 4 Replies
View Related
Jun 1, 2013
I am using 11.2.0.1 database and I have all my datafiles,control files,redo logs in /home/oracle/oradata and i would like to completely move all the files to /data/oradata folder.
What is the best method? (without losing old backup's , without reset log , no new incarnation,no new DBID) can it be done online /offline?
Is it recreating control file with new location and open database without resetlog the best method?
View 2 Replies
View Related
