SQL & PL/SQL :: How To Write Package For Deletion
Apr 21, 2012how to write the package for deletion?
View 1 Replies
ADVERTISEMENT
Deletion Of Archives From Asm
Apr 11, 2013I want to delete archivelogs from ASM and want to schedule a script for same in crontab.
Also i removed archives manually from asmcmd.But when i runned below command,i didn't observed any change in the free_space.
select total_mb,free_mb,(total_mb-free_mb) used from v$asm_diskgroup;
Deletion Of Contents Of Tablespace
Jun 15, 2012How can we clean a tablespace(delete all the contents) without dropping the tablespace.
View 3 Replies View RelatedForms :: Prevent Deletion Of Record
May 6, 2010Form has insert and delete enabled.
I would like to prevent the deletion of a record based on some column value.
Something like:
IF (COLA value < 10) THEN
SHOW ALERT;
RECORD IS NOT DELETE
ELSE
DELETE RECORD AS NORMAL
END IF;
I want this to happen even before the record is marked for deletion, but I don't know where to put it. I tried a pre-delete trigger, but it does not work they way I want.
Forms :: To Restrict Record Deletion
Nov 6, 2011When i opened the form and after querying the records if I press CTL+ Down Arrow the record is deleted.
This is the normal runtime form behavior. When ever pressing CTL+ Down arrow I don't want to delete the record, I tried with KEY-DELREC but it is not happening.
Data Guard :: Archive Log Deletion
Mar 8, 2013I have a RAC system with DR set-up, this is a test environment and it doesn't have any backup, why DR is required but it exist. Since this is a test a lot or archives gets generated and deleting the archives has become a daily job for this server manually.
I want have a script to delete archive logs which is in non-ASM (i.e. filesystem) after ensuring that the archive log has been applied in standby database. If this can done only by RMAN.
Networking And Gateways :: Deletion Of LDAP Entry
Nov 25, 2010We had an escalation wherein one of team members accidentally deleted an LDAP entry for a database. We use Oracle Net Manager to add/delete the connect descriptor.
Are there any logs using which we can find out as to who deleted the entry.
Application Express :: Deletion Of Public Reports In 4.0?
Sep 4, 2012As far as I know, in APEX 4.0 no other user other than the one who created a Public report can delete it. Can this behavior be changed somehow?
View 3 Replies View RelatedDeadlock In Database Because Of Concurrent Data Deletion
Feb 4, 2013I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.The example and the table structure is given below.
Col1 of tab1 is foreign key to col1 of tab2.
Commit will not be done until all the below dml scripts are executed in both environment.
1st session:
Delete from tab1 where col1=1001;
Delete from tab2 where col1=2001;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1002;
Delete from tab2 where col1=2002;
Result: Deletion successful
1st session:
Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1004;
Delete from tab2 where col1=2004;
Result: Query is executing for a longer time.
1st session:
Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;
Result: Query is not executed and throws Deadlock in the back end.
PL/SQL :: Find Out In Database Package Where Package Is Installed
May 28, 2013I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
Database Keeps Running Even After Deletion / Corruption Of Control Files
Jan 21, 2011I was carrying out an experiment in order to crash the database and recover it.
The database was running, I moved the control file to another location and to my surprise the database was still running. I tried issuing checkpoint and transaction but it didn't affect database operations. I tried doing log switch. It completed successfully. According to my understanding and Oracle Certification books database was supposed to crash. But it didn't.
I tried this not only on RHEL, Windows XP but also in Solaris 5.10. The database version is Oracle 10.2.0.4 Standard Edition.
SQL & PL/SQL :: Trigger Fires On Deletion Of Uncommitted Data And Changes Saved
Dec 26, 2012Maybe you can tell me if you think it's right or wrong for Oracle to behave like that. What basically happened is that I've set up a trigger to capture deleted rows from testtab into testtab_del.
I have inserted and immediately after - deleted a row from testtab. Then I inserted another row, committed, and checked my testtab_del table.
I've seen that val1 was inserted and committed into testtab_del. This happened in spite of the fact that this row never existed as a *committed row*.
What do you think about this behavior in this scenario? Works as designed or not?
SQL> show user
USER is "ANDREY"
SQL> col tcol for a10
SQL> drop table testtab;
[Code]....
Server Administration :: Tablespace Size Is Same As Before Deletion Of User?
Oct 1, 2012I have deleted a user with CASCADE option.
After deletion, the size of tablespace should be reduced.
But the tablespace size is same as before deletion of user.
Currently , that USER is deleted and not present in the database . Still space is not released after user deletion.
Replication :: Oracle Materialized View / Deletion Of Records
Nov 13, 2009One question reg Materialized views.
If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion? I believe the answer is yes. In that case can we ensure that this delete does not happen?
Is there anyway we can prevent MView refresh from deleting the records that is once inserted even if we delete the same records in source DB?
Backup & Recovery :: RMAN - Archivelog Deletion Policy Not Working?
Oct 4, 2012I'm on a Windows server 2003 R2 64 bit, database is 11.1.0.7. From RMAN connected to the target and the catalog, this is a show all;
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:oracleadmin
[code]....
I'm running incremental backups, a level 0 on sunday and a level 1 the other days of the week, this is the ctl file:
LEVEL 0:
connect target /@ORCL
connect catalog rman/rmanpw@rmancat
RUN
{
[code]....
The level 0 does delete the archive logs (because of the delete input). Each backupset has two copies of the archive logs (which is what I want), and I expected the logs to be deleted after being backed up 2 days (each day is twice, 2 days = 4 times). But it's not deleting the logs, even after 6 runs.
Data Guard :: Configure Archivelog Deletion Policy To Applied On Standby
Nov 16, 2012I have a DR setup with the following configuration
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
I dont want to backup the STDBY DB but I want the ARC files to be removed when applied so my flash area does not fill up. Is there some command(rman or not) that can fire off this policy?
SQL & PL/SQL :: Global Exception In Package Available Outside Package?
Jan 15, 2012I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?
Feb 7, 2012We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
How To Write Exception Handler For PL / SQL
Jan 22, 2007How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"
View 2 Replies View RelatedCan't Write SQL Scripts On Oracle G10
Apr 8, 2009I just install the oracle g10 on my laptop, which runs windows XP and it seems to work fine BUT it will not allow me to write Scripts I go to the Script Editor, i can write on the "Script Name" but below i cant ever put the cursor to write the script!
View 3 Replies View RelatedHow To Write Audit Script
Oct 14, 2008To write a audit script, that will analyze SQL usage and the performance of both individual statements and the overall memory utilization.
View 1 Replies View RelatedSQL & PL/SQL :: How To Write Correlated Sub Query
Sep 13, 2010I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
How To Write Trigger In Oracle 10g
Apr 12, 2011I am using SQL*Plus to run this Oracle10g. Here is my problem I have to solve: After a new record is added to the task table, write a trigger that reports the new project balance to the user while updating the customer balance to reflect this additional task cost. Insert a new record to show the trigger works. I somewhat understand triggers,I think I have to combine tables to solve this. Here are my tables/attributes I can use to solve it:
CREATE TABLE customer
(
custnoNUMBER(3)CONSTRAINT ccustpk NOT NULL PRIMARY KEY,
custname CHAR(20) CONSTRAINT ccustnamenn NOT NULL,
phone CHAR(12),
curr_balance NUMBER(9,2) CONSTRAINT ccustbalancenn NOT NULL
[code]...
SQL & PL/SQL :: How To Write LAST_INSERT_ID () In Oracle
Sep 25, 2013I have a procedure in mysql that need to be converted to oracle.I tried to convert it ,everything looks good except the function LAST_INSERT_ID().
mqsql procedure
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_ac_message`(
IN in_process_run_id INT,
IN in_processID INT,
IN in_messagename VARCHAR(25),
IN in_message VARCHAR(200))
BEGIN
[code]......
oracle procedure
create or replace
PACKAGE body Mysql_To_Oracle
IS
Procedure Add_Ac_Message(In_Process_Run_Id In Number,
In_Processid In Number,
In_Messagename In Varchar2,
In_Message In Varchar2)
[code]....
Error:
ORA-00904: "LAST_INSERT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
SQL & PL/SQL :: How To Write Column To Row In A Query
Nov 17, 2005How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
PL/SQL :: Write Clob Or Xml To A File?
Jul 9, 2013I have read the following article:[URL] 11070I want to know wherer if there exists a possibility of write a clob or an xml into a file on disk, if we do not have the CREATE ANY DIRECTORY privilege. Many functions, like UTL_FILE.FOPEN, or dbms_xslprocessor.clob2file, or dbms_xmldom.writetofile, need an Oracle directory to be created (with CREATE OR REPLACE DIRECTORY...). But if we don't have this privilege, is there a possibility to export a clob into a file as xml (the clob contains 100% xml, but this is the column data type, CLOB) if we don't have that privilege?The clob data contains 48200 characters.
View 8 Replies View RelatedTo Write Generic Way Of Oracle 10g PL / SQL Code
Dec 14, 2010I Would like to delete a records based on the parent / child relationship. So I would need to delete a record from parent table , first delete inner most child record then other child then master. To write generic way of Oracle 10g PL/SQL code.
View 2 Replies View RelatedMultiple Processes To Write File?
Sep 10, 2012In PL\SQL program, I am writing information from one table to file. In my current architecture, I am writing the information to approximately 1000 files.
If I put the database write operation in another package and in another method and call this method from my PL\SQL program asychronously, can that increase performance?
SQL & PL/SQL :: Write A Trigger To Validate V_status For Each ID
Dec 30, 2010I want to write a trigger to validate the v_status for each ID. below is the example
SEQ_NO ID V_STATUS
11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 C
For each unique id there will be only one F .. if some one tries to update the 2nd F to the same ID it will show the error message that ID is already F.
SEQ_NO ID V_STATUS
11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 F
SQL & PL/SQL :: Write Log When Procedure Execution Is Successful
Aug 10, 2011My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.
Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".