SQL & PL/SQL :: Delete Bulk Records In Tables From Which Path Can See How Much Space Is Free
May 5, 2010
Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
For eg.
CREATE TABLE MLOG$_TEST
(
ID VARCHAR2(64 BYTE),
SNAPTIME$$ DATE,
DMLTYPE$$ VARCHAR2(1 BYTE),
OLD_NEW$$ VARCHAR2(1 BYTE),
CHANGE_VECTOR$$ RAW(255)
)
So is MLOG$_TEST oracle's internal table.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
View 9 Replies
ADVERTISEMENT
Oct 3, 2012
Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
View 9 Replies
View Related
Jun 27, 2011
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexDelete
ORA-20000: Oracle Text error:
DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA
DRG-50857: oracle error in drexrdml
ORA-01031: insufficient privileges
[code]....
View 2 Replies
View Related
Feb 26, 2013
for some reason,i want to know which data block contains free space,or which table/index contains free space.
View 6 Replies
View Related
Nov 5, 2013
is it possible to get information about free space on drive/path for example with some DBMS_*, UTL_* package or with stored function based on Java
(e.g. CREATE OR REPLACE FUNCTION GetFreeSpace(driveLetter IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'DiskSpace.getFreeBytes(java.lang.String) return java.lang.String') but for JDK 1.3
View 8 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Jul 23, 2011
How do we find the free space in tablespaces in a standby database
View 4 Replies
View Related
Jan 16, 2013
I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.
Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.
select sum(bytes / (1024 * 1024 * 1024)) "Size (GB)" from dba_free_space where tablespace_name ='&tbs_name'
I am using following command to drop partition
ALTER TABLE table_name
DROP PARTITION "partition_name"
UPDATE GLOBAL INDEXES;
What should be done to increase free space shown for tablespace after partition drop?
View 2 Replies
View Related
Jul 9, 2012
I've installed GI 11.2.0.3 on AIX 7.1.And today I noticed strange ASM's behavior:
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*2285768*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *188616* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
I guessed that was because of LUN size (it was exceed 2 TB)After that I dinamically shrinked LUN size on our external storage, rebooted and perfomed cfgmgr command on both nodes. But I still have no enough free space.
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*1992294*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *1992294* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
View 2 Replies
View Related
Oct 11, 2013
Some trace files were deleted with rm command. Obviously they were active and now the free space does not free up (over 9GB). How can this be fixed without database interruption?
View 4 Replies
View Related
Oct 29, 2012
try to evaluate it as IMDB cache.I am facing this error repeatedly. I have increased perm size from 6.25 GB to 10 GB.After inserting about 460.000 rows I get the error again. Is it possible that 460.000 rows need 3.75 GB space?
In Oracle database these rows occupy about 200 MB space.
View 2 Replies
View Related
May 16, 2011
what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));
Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number,
P_err_msg OUT NOCOPY varchar2) as
v_count number;
Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE;
Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;
[code]...
View 9 Replies
View Related
Feb 24, 2009
I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.
I cannot run the below code considering the amount of records or rows in the table A.
Begin
Delete from A where trunc(date_column) < '01-FEB-2009';
commit;
End;
where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?
View 14 Replies
View Related
Mar 24, 2011
I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?
delete from
TM_060_SFS_TEST
WHERE
rowid in (
SELECT
[code]......
View 1 Replies
View Related
Oct 9, 2012
I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
View 9 Replies
View Related
Feb 21, 2011
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
View 6 Replies
View Related
Dec 5, 2011
I am trying to write an update using two tables, one table contains the list of updates to be applied, and the second contains the old data. I have a working solution but I was wondering if there might be some other better way to accomplish this task.
CREATE TABLE foo
(
foo_id NUMBER,
foo_val NUMBER
);
CREATE TABLE foo_change
(
foo_id NUMBER,
foo_val NUMBER
[code]...
The update works, but I thought there may be a better way I just don't know about.
View 2 Replies
View Related
Jun 14, 2013
I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :
YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate
View 5 Replies
View Related
Aug 13, 2010
I am trying to bulk update records in oracle using XML , front end is vb.net.Now the problem when i updating for 1000 - 5000 records on my development server. Its getting updated.
But when we are updating on the production server for 100000-200000 records , we receive error
"ORA-01460: unimplemented or unreasonable conversion requested "
View 1 Replies
View Related
Feb 15, 2012
How to delete flashback log and to release the space?
I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
View 6 Replies
View Related
Sep 16, 2010
In Oracle database pl/sql 11.2: describe the differences between :
records,
tables, and
tables of records.
View 11 Replies
View Related
Sep 4, 2012
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;
I'll show the toP1 below:
SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE aloc used reclaim
-------------------------- --------------------------- ------------------ ---------------- --------
USR_CONTROLFIN CFI_PORTADOR TABLE 744 0 743
Fine, then i shrinked it:
10:06:39 brunos@gf> alter table usr_controlfin.cfi_portador enable row movement;
Table altered
Executed in 0,047 seconds
10:06:57 brunos@gf> alter table usr_controlfin.
View 19 Replies
View Related
Jul 3, 2010
I am selecting around 10000 records in a recordset using an sql query.I need to delete these 10000 records from 10 different tables as each of the table have these records.I can use a FOR Loop for each record one by one to delete these 10K records from each tabel but i was wondering if i can delete there 10K in one go so that the program would Loop Only once rather thatn 10K times.
Mine Delete code looks like:
if x.first is not null then
for i in x.first..x.last loop
delete tbl_A where trade_id = x(i).trade_id;
delete tbl_B where trade_id = x(i).trade_id;
[code]...
Where x is the recordset having around 10K records.Is there any way i can run the loop only once rather than one by one for 10K times.
View 5 Replies
View Related
May 1, 2013
Sqty is sold qty , rqty is return qty , sdate is sold date , vno is billno .
When we enter sdate , this will fetch the products sold on the given date , and total sold qty .
If some products are returned which are entered in rqty , then this should delete all the records of the product on that sdate , and these products as spread along several billno's .
View 19 Replies
View Related
Sep 29, 2010
say I have duplicate records in TABLE3, can I use following SQL to delete the duplicates?FYI, BUS_FID is unique number column.
TABLE3
------------------------
ori_id*************id***********r_f_a****r_t_a*******bus_id
72840000040572*****740040572****255******267*********1
72840000040572*****740040572****255******267*********2
2840000040572******20040572*****25*******27**********3
7840000040572******70040572*****2********6***********4
DELETE FROM TABLE3 WHERE BUS_ID NOT IN (SELECT MIN(BUS_ID) FROM TABLE3 GROUP BY ORI_ID);
consider TABLE3 has millions of millions of data, say 40 millions.Update, I have tried it and it is very slow...
View 5 Replies
View Related
Mar 14, 2012
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
View 5 Replies
View Related
Apr 13, 2011
I need to delete duplicate records from a table (indeed they are multiple duplicates).
Table Data
IDGroupQty
1KK30
1KK0
1KK19
2AA0
2AA30
3AA0
3AA30
3AA30
3AA9
My aim is to delete duplicates out of above data, with the below condtions.
1) first record with value 30 and then with value 0.
2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records.
3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.
I have written a query like below.
SELECT id,
unit,
RANK ()
OVER (PARTITION BY id, unit
ORDER BY id, unit)
num
FROM temp;
with the above query, i am unable to mark this dynamic duplications.
View 10 Replies
View Related
Jul 16, 2010
while i'm inserting a records into the table tablespace has got full and next statement which is commit is executed
so now i need to remove the data due to the last insert into the table.
Table size is very huge and i was unable to find the records any criteria.
View 7 Replies
View Related
Aug 30, 2010
How can i delete duplicate records from the table.
View 2 Replies
View Related
Dec 1, 2010
I wish to delete records found with function MINUS from file1, which is a result of records not found in file2.
Both codes are taken way too long.
ps. none of the columms are unique, and some records have no value in some columns
DELETE
FROM file1 y
WHERE EXIST (SELECT *
FROM (
SELECT a.col1, a.col2, a.col3, a.col4
FROM file1 a
[Code]....
View 38 Replies
View Related
