Does Shrink And Move Both Adjust The High Water Mark
Nov 4, 2013
I am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
I have deleted lot of records in a table.Would oracle be able to insert in the empty blocks generated from deletion of records without bringing the high water mark down.
I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables. Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.
I am re-developing some forms which were designed before on some other machine.
I cant able to run the form and not even to compile them. I am getting an error message like THE OUTPUT OF THE FORMS ARE UNABLE TO ADJUST.
I have checked the canvas dimensions like height and width that they are matching with that of windows of the form. I have also checked the values in formweb file. Everything is correct up to my knowledge. I have also ckecked the setting of LCD screen like it is 1024 X 768 resolutions.
"SUM_TOTAL_RECEIVED" ,this is Item name and i set all Calculation Properties correctly and when compile the form , i got an error
FRM-30405: Datatype for Sum/Avg/Variance/Stddev summarized item must be Number. Item: SUM_TOTAL_RECEIVED Block: RHCLANDEDCOSTDET_PROMPT FRM-30085: Unable to adjust form for output.
I want to adjust 2900 against certain amount from table on fifo basis.
DROP TABLE ABC CREATE TABLE ABC(ID NUMBER PRIMARY KEY,AMT NUMBER); INSERT INTO ABC VALUES(1,1000); INSERT INTO ABC VALUES(2,2000); INSERT INTO ABC VALUES(3,3000);
Can i do it using SQL. I know that it can be done using PL/SQL but i just wanted to confirm if its possible using SQL.
I've created a form that is working on the basis of Invoice Processing. There I want to get the sum of all the total of the products * quantity..I'm getting this error
FRM-30425: Summarized database item must reside in a block with Query All Records or Precompute Summaries set to Yes. Item: TOTAL Block: SBM_SALES_PRODUCTS FRM-30085: Unable to adjust form for output.
if the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?
Whats the difference between commands? >alter table my_table shrink; -- move HWM >alter table my_table shrink compact; -- not move HWV
I have a tablespace which has around 32gb space consumed. But if i check the used space then its only 16GB. When i tried to resize the datafile it throws the error
ORA-03297: file contains used data beyond requested RESIZE valueAs per my understanding there are not continous blocks which are there in datafile due to fragmentation may be and there by not able to resize it. If i export the tablespace using datapump and reimport this will release the space.
But i want to know if there are any alternative ways to do the same.
Well, I have a oracle database 10g and the tablespace INDX was getting up to 32 GB size. Now I added second datafile to the space, but can I shrink this space? In my view this space is responsible for indexes, right? There is a command to rebuild the indexes or there's another trick?
SQL> Alter Table tb_hxl_user Shrink Space Cascade; Alter Table tb_hxl_user Shrink Space Cascade * ERROR at line 1: ORA-10635: Invalid segment or tablespace type
SQL> desc tb_hxl_user; Name Null? Type ----------------------------------------- -------- ---------------------------- STATEDATE NOT NULL DATE USERNUMBER NOT NULL VARCHAR2(13) PROVCODE NOT NULL NUMBER
I am trying to use shrink to reclaim space on some tables:
-First of all i am using this query to verify which tables are eligible to shrink: select segment_owner, segment_name, segment_type, trunc(((allocated_space)/1024)/1024) "aloc", trunc(((used_space)/1024)/1024) "used" , trunc(((reclaimable_space)/1024)/1024) "reclaim" from table(DBMS_SPACE.ASA_RECOMMENDATIONS()) where segment_owner='&owner' and segment_type='TABLE' order by reclaimable_space desc;
Name Null Type ------------------------------ -------- ------------------------ ENTITY_ID NOT NULL VARCHAR2(100 CHAR) ENTITY_TYPE_ID NOT NULL NUMBER SOURCE_ID NOT NULL VARCHAR2(512 CHAR) XML_SCHEMA_ID NOT NULL NUMBER JOB_ID NOT NULL NUMBER FINGERPRINT NOT NULL VARCHAR2(100 CHAR) ENTITY_XML_DATA CLOB() ARCHIVED NUMBER(1) CREATION_DATE TIMESTAMP(6) MODIFICATION_DATE TIMESTAMP(6) ARCHIVING_DATE TIMESTAMP(6) CREATED_BY VARCHAR2(50 CHAR) MODIFIED_BY VARCHAR2(50 CHAR)
The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?
The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992
TABLESPACE_NAME FILE_ID FILE_NAME Size(MB) ------------------ ---------- ------------------------------------------------------- ---------- CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688 CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480 CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile. db_block_size is 16KB. [code]....
in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?
It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;
Table created. SQL> alter table t1 enable row movement; Table altered. SQL> alter table t1 shrink space; Table altered. SQL> create index i1 on t1(c2); Index created.
SQL> alter table t1 shrink space; alter table t1 shrink space
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.
I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:
zip plus4 low plus4 high 10000 001 003 10000 008 010
My requirement is while sending a data file from oracle to mainframe, first 3 bytes for the header row should contain low values and trailer should contain high value.
How to pass oracle values to mainframe high and low values ?
I have a priority column(possible values are 1 or 0) in a table where
i need to get 70% of high(1) and 30% of low (0) and max i can fetch for select is 50 records.
Eg1: Total if i have 60 in which 20 high and 40 low then 70% of 20 = 14 and remaining should be taken from Low i.e. 36 from low. so total will be 50 transactions.
Eg2: Total if i have 60 in which 40 high and 20 low then 70% of 40 28 + remaining should be taken from Low i.e. 22 from Low.
Eg 3: If i don't have any high then total should be picked from low vise versa.
I have below query but it is having problem when there is no low priority.
SELECT ID,PRI FROM temp tbl WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1 UNION ALL SELECT * FROM temp WHERE pri = '0' AND ROWNUM < 50-(SELECT COUNT(*) FROM temp WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1)
I have setup of two node (prod-db1, prod-db2) clustered database 11gR2 on windows 2008 R2 server. Everything is working fine at this setup.
My question is: Is there a way to make the Enterprise manager Database control run and be available at both the nodes independently. What I see that even at node 2 (which is prod-db2) the EM-DBControl is (https://prod-db1:1158/em) - which means the agent is running at node 1 (prod-db1) only.
My question is that how to make the EM-DBControl also run separately at prod-db2. My idea is to make the high availability of EM-DBControl (in case Prod-db1 machine is down).
I am in the very early planning stages of a project the goal of which is to identify separate organizations which may in fact be the same organization.
Our first implementation of this task was a process designed to look for a few thousand organizations in a pool of a few hundred thousand organizations. To accomplish this we made heavy use of Oracle's Text index as well as a custom index type we created which utilized n-grams. This approach worked quite well for on-demand editing of the organizations, in which a user might log in and say in addition to what we already know about organization A we also know x, y and z does that change anything and worked acceptably well for the bulk processing we did on our "known" information once a week running for a couple of hours on the weekend.
We have now been tasked with reworking this initial implementation only now we want to look at a set consisting of several million organizations for potential matches which exist within the set. As in our initial implementation we will be breaking what we know about organizations into groupings so we aren't comparing a phone number to an email address and normalizing the data as much as we can so we ignore things like case and punctuation. Even after all this we are still talking about looking for similar values in a group which might be in the tens of millions (some types of data will have more than one value per organization).
My initial thought on the problem is to use n-grams though not in the way we did in the past. The basic idea here is that we break the search values up into all the substrings it is made of and look for other values which have a high number of those substrings in common.
SQL & PL/SQL was the best place for the question, but I could not think of a better one.
I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.
This is the query:
SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID
