declare
min_as_of_dt date;
max_as_of_dt date := to_date('30-SEP-2012', 'DD-MON-YYYY');
begin
DBMS_OUTPUT.ENABLE(10000000);
[code]......
Problem: rows are not getting deleted after running this script.
how to retrieve deleted rows in oracle?
View 7 Replies View RelatedData purging and loading into different table.
Currently we a have a table with size 300gb, and we wanted to remove this table to release storage. but have to keep the 6 month data which would be around 3 million per day ( 3 million X 180 days).
i have the following questions.
1) what would be the best strategy to move this 6 month data from Table A (will be removed) to table B ( will be online table).
2) later we want to delete the data from table B which is 6 month older every day or week ( frequency is still to be decided)
note that We cannot use the partition as don't have a access to create and delete partition. what would be the best strategy for deleting the data?
I want to purge scott's recycle bin as user "SYSTEM". Because I do not know the user's password. How do I achieve it?
SQL> purge scott.recycle bin;
purge scott.recycle bin
*
ERROR at line 1:
ORA-38302: invalid PURGE option
We are facing space crunch in our 4 development 11i ERP environment servers and decided to reclaim the space . We are having 11.1.0.7 Database . What we observed is, there are many objects present in the recyclebin and we went on to purge the reclybin of MSC user.
SQL>select owner,sum(space)* 8 / 1024/1024 "Size in GB" from dba_recyclebin group by owner order by sum(space) ;
OWNER Size in GB
--------------- ----------
MSC 127.672485
IRCUS 22.5664063
GL 2.98492432
APPS .38079834
XXDII_ESB .192993164
APPLSYS .001159668
TMPBKP .000427246
But even after purging the recycle bin of MSC User , we didn't find any changes in DBA_FREE_SPACE for those tablespaces in which recyclebin objects were present. We would have seen the freed space in dba_free_space.
I have a major problem my oracle_home/bin directory was delete mistakenly by on of our user. Because i cannot use all oracle utitilessqlplus,dbca,netca,netmgr. how do i the cover this problem?
View 2 Replies View Relatedi want to insert values in another table whatever i deleted. i don't want whole information like backup, i just want deleted information only. whenever any 1 will try to delete any information on my database ,easily i would get to know what,when he has deleted .
View 14 Replies View Related1)How can i know that in a table when i modified a row or deleted a row and which row i inserted when i want to know the particular time
can it is possiable if possiable then tell me how.
2)Is there any difference between 9i merge and 10g merge command ?
I have deleted all the records from the table.And I have committed.Now I want to get all the records back.
View 16 Replies View RelatedHow to find out the tables that are frequently getting deleted?
View 2 Replies View RelatedI deleted a table in oracle sql 11g about two weeks ago and commited. Didn't realise that I would need it later on and now I needed. is there a way a can get reverse this.
View 13 Replies View RelatedI am new to oracle database. I wanted to know if it is possible to recover a deleted column of a table. If yes, how can it be done? I tried flashback but it seems that flashback doesn't support recovery of deleted column.
View 2 Replies View RelatedI am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
Somebody deleted records from 10,12 tables in one of the schema. I found in one of the forum that table can be restored also if it exists in the recycle bin. I checked by querying:
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
> select type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin WHERE TYPE='TABLE' and droptime like '%2012-07-31%';
PHONE TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
TABLE YES YES 2012-07-31:10:23:08
- -
- - I created a test table and dropped it and restored from recycle bin using following flashback table test query....but in my case tables are not dropped...these tables are shoing 0 records. How can I recover only records from recycle bin tables?
SQL> flashback table test to before drop;
Flashback complete.
DB=10.2.0.5 on RHL
I have a trigger which monitors the deletion activity on the table. But I would like to re-insert those records to the in the table using the same trigger.
View 1 Replies View RelatedI am trying to create a trigger to stop events from being deleted if the date is before the current date. This is the SQL syntax for my table
CREATE TABLE event
(event_id numeric(5) not null,
concert_id numeric(5) not null,
event_date date,
);
What I have attempted so far is:
CREATE OR REPLACE TRIGGER event_deleting
BEFORE DELETE ON event
FOR EACH ROW
BEGIN
IF DELETING THEN
[code]......
This gives me a few warnings, the first is that a DECLARE is missing and the second is a INTO is missing.
A full, online backup (database, archived redo logs, and control file autobackup) is done with rman ("nocatalog", Oracle 9i on AIX 5.2) daily. A media manger is not in use, so the backup is written to disk, then written to tape using an operating system utility. There is room on disk for two full backups, so "redundancy = 1" and obsolete backup sets are deleted to free up space immediately before running the next backup. If the backup(s) on disk are lost and the tapes containing these backups are lost, will it be possible to restore a tape containing older "deleted" backups (using an operating system utility) and catalog each backup piece in the current control file? In other words, can backups considered "deleted" in the control file be reintroduced if they're available on tape?
View 4 Replies View RelatedHow to avoid this error. FRM 40102 Records must be inserted or deleted first
i have used next_record.If i remove this it is working.but i need to use next_record built in.
I'm getting below error. how can we avoid this error?
ERROR: frm-40102 record must be entered or deleted first
### Changes made ###
1 week before we did a change on tablespace segment management - from MANUAL to AUTO by following method:
1. create INVD2 & INVX2 & LOBD tablespace.
2. Move TABLE from INVD to INVD2.
3. Rebuild INDEX from INVX to INVX2.
4. Move LOBSEGMENT from INVD to LOBD tablespace.
5. After confirm no segments exist in old tablespace, offline and drop INVD & INVX.
6. Change default tablespace for INV user to INVD2.
7. RENAME TABLESPACE INVD2 to INVD, INVX2 to INVX.
8. Change default tablespace for INV user to INVD back.
9. Run Gather Schema Stat for INV using UNIX scheduler which work usually. However, error ended with ORA-03113 & ORA-03114.
10. Manual execute with same statement the following day, procedure completed successfull.
After 1 week later, inventory forms detected error FRM-40735 in all forms. Checked the gather schema stat job was run in the morning before user feedback..
AFter refer notes from metalink, I understand this is a bug where RENAME of the tablespace could not rename as the previous one, as the deleted entry is still exist in sys.ts$?
There is no segments exist in the deleted tablespace, or any user default tablespace is assigned to the deleted tablespace.
My Question:How can we delete the deleted entry from sys.ts$?And should we rename the tablespace from INVD to INVD3 (or can we use back INVD2) to avoid any unforseen error again?
Is there any way I can find out what caused the database to crash; either a history of commands executed within the database, I lost my bdump directory before the scheduled backup ran and the only logs available are after I re-created the directory.
SQL> startup
ORA-00444: background process "PMON" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .
SQL>
[code]....
I ran into the message RMAN-08138: WARNING: archived log not deleted - must create more backups during a backup. From what I understand this is caused when logs are removed that are needed for recovery.
rman target / nocatalog <<EOT
CONFIGURE CHANNEL DEVICE TYPE DISK clear ;
CONFIGURE CHANNEL DEVICE TYPE TAPE clear ;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' clear ;
CONFIGURE RETENTION POLICY TO recovery window of 14 days;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
[code].....
A datafile was deleted from a partitioned table which has 31 partitions , that datafile contains data from day 25 to day 31 st I am not having a backups of this database. but I have all the dumps of that table what is the solution to get those 6 partitions data back.
View 9 Replies View RelatedThe SQL Query portion of Authorization Schemes are deleted or set to NULL after editing. Re-entering the SQL Query will not save the SQL, so the SQL Query is lost.
I had to update WWV_FLOW_SECURITY_SCHEMES.ATTRIBUTE_01 via sql to restore and modify the SQL Query portion of the Authorization Schemes.
I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.
CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);
[Code]....
version: 10.2.0.4
OS: windows server 2003
I am not able to delete one month old archive log file manually on windows which doesn't having info about the standby on v$archived_log view of primary database. the sequence were already applied to the standby database. It shows the status as deleted in v$archived_log. while deleting the file manually. it showing an error like another program or person is using it.
Operating System - WindowXP
oracle version 10.2.0.1
I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.
When i tried to open database, it failed to start. Database is in mount mode.while trying to open, it gives message -
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.
Oracle 11G...After creating a new datafile, it was accidentally deleted at OS level (AIX).Because it was new there are no backups of it.Now tablespace thinks there are 3 datafiles in it, but one of them is missing.
Is it possible to recreate an empty data file on-the-fly?It has let me set the datafile to offline, but wouldnt let me do anything else.
Now my backups are failing as it recognises :"the status of database file is RECOVER".
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 RelatedI 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]....
