Deletion Of Contents Of Tablespace

Jun 15, 2012

How can we clean a tablespace(delete all the contents) without dropping the tablespace.

View 3 Replies


ADVERTISEMENT

Server Administration :: Tablespace Size Is Same As Before Deletion Of User?

Oct 1, 2012

I 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.

View 4 Replies View Related

Backup & Recovery :: Dropping Tablespace Including Contents And Datafiles

Jun 17, 2013

1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.

2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.

List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF

3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"

4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.

SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
[code]....

how to recover those tablespace's datafile?What happened to earlier tablesapce backup?

View 9 Replies View Related

Deletion Of Archives From Asm

Apr 11, 2013

I 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;

View 6 Replies View Related

SQL & PL/SQL :: How To Write Package For Deletion

Apr 21, 2012

how to write the package for deletion?

View 1 Replies View Related

Forms :: Prevent Deletion Of Record

May 6, 2010

Form 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.

View 8 Replies View Related

Forms :: To Restrict Record Deletion

Nov 6, 2011

When 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.

View 3 Replies View Related

Data Guard :: Archive Log Deletion

Mar 8, 2013

I 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.

View 6 Replies View Related

Networking And Gateways :: Deletion Of LDAP Entry

Nov 25, 2010

We 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.

View 1 Replies View Related

Application Express :: Deletion Of Public Reports In 4.0?

Sep 4, 2012

As 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 Related

Deadlock In Database Because Of Concurrent Data Deletion

Feb 4, 2013

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Parsing BLOB Contents

Mar 28, 2013

I have some BLOB contents in the format of...

DK99F17,AA,032820130840,Other
ABCD,AA,032820130840,OV
AAZ123,BC,032820130932,DWL
CBA12345,ZA,032820130939,Other
Each BLOB is associated to a file name in the format...
03282013100002_thisfile.txt

The blob for each file may be zero rows to n rows in size, but typically there are 2 to 5 rows (four rows were shown in the rows above).The following kind of gets me there, but not quite as it splits up the BLOB rows at the comma and not the line break (HEX=0D0A / CRLF).

with rec as
(select fs.file_name, utl_raw.cast_to_varchar2(fs.file_data) file_data
from tada.files_store fs
where fs.file_name like '%citations.txt'
and trunc(fs.date_created) = to_date('26-MAR-2013','DD-MON-YYYY'))
[code].....

View 4 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

Database Keeps Running Even After Deletion / Corruption Of Control Files

Jan 21, 2011

I 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.

View 1 Replies View Related

SQL & PL/SQL :: Trigger Fires On Deletion Of Uncommitted Data And Changes Saved

Dec 26, 2012

Maybe 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]....

View 17 Replies View Related

Replication :: Oracle Materialized View / Deletion Of Records

Nov 13, 2009

One 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?

View 3 Replies View Related

Alter Table Column And Its Contents?

May 4, 2011

I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.

I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.

View 2 Replies View Related

SQL & PL/SQL :: How To See The Contents Of Wrapped Package Body

Mar 31, 2011

I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.

View 1 Replies View Related

SQL & PL/SQL :: Table Contents - Unavailable Date

Oct 17, 2012

i have table A i.e. contents

----------------------
1-jan-2012 x
2-jan-2012 y
4-jan-2012 x
7-jan-2012 x

I need to discplay below:

1-jan-2012 x
2-jan-2012 y
3-jan-2012 0
4-jan-2012 y
5-jan-2012 0
6-jan-2012 0
7-jan-2012 x

View 1 Replies View Related

Server Utilities :: How To Know Dmp File Contents

Sep 24, 2011

Is there a way to list contents of a dmp file(using expdp generate),which objects(tables/vies/procedures/packages) does it contain?

View 4 Replies View Related

PL/SQL :: Adding Columns To Count Contents Of Other?

Aug 31, 2013

how to incorporate counts into my current SQL code  that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these  columns). I don't really care if the count value repeats in every row for that person.

 Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food  from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars',  T3.Cars  from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Table With Different Contents

Mar 31, 2010

I am having a table with contents like

item id, name
100,s_enter
100,p_enter
200,s_enter

i would like the output to be

Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)

I am working with basic case but thats not working.

select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table

View 3 Replies View Related

Backup & Recovery :: RMAN - Archivelog Deletion Policy Not Working?

Oct 4, 2012

I'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.

View 4 Replies View Related

Server Administration :: How To Read Dump Contents

Jan 4, 2012

How to read the treedump contents of a index IDX_TB_TEST_N1?

SQL> select object_id,object_name from dba_objects where owner='HXL';

OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
51786 IDX_TB_TEST_N1

alter session set events 'immediate trace name treedump level 51786'

/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

[code]...

View 6 Replies View Related

Server Administration :: Contents Of Redo Log Files

Feb 1, 2011

I have some doubts about redo log files,

1) Can we fetch 'select statements' from redo log files through the use of log miner utility or any other?
(I think redo log file contains only insert,update,delete and DDL/DCL commands only)

2) If "No" to the above answer then how can i fetch all select statements fired on the system for a day or particular time.
(setting of sql_trace may be the one of them, but can it be possible for system level)

View 4 Replies View Related

Data Guard :: Configure Archivelog Deletion Policy To Applied On Standby

Nov 16, 2012

I 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?

View 6 Replies View Related

Client Tools :: SQL Developer Cuts CLOB Contents?

Aug 23, 2011

I'm having a problem while exporting an DB to file. I have a certain table with a CLOB column which values are cut when I use the Export option on SQL Developer.

View 1 Replies View Related

Backup & Recovery :: Restore Datafile With Its Contents Without Using RMAN

Oct 13, 2011

I have no backup taken in my oracle server.Today morning i created a data file and unknowingly deleted it. Now i need to restore the datafile with its contents without using RMAN.

View 14 Replies View Related

Application Express :: How To Insert Contents Of CSV File In Table

Sep 16, 2013

I'm starting with APEX, I would like to know how to insert the contents of a file. Csv in a table, how to map a file to a table, how to insert file contents. Xls in a table.

View 1 Replies View Related

Application Express :: Capture Contents Of Page - Copy To Clipboard

Jan 31, 2013

I just need to run javascript to capture the contents of the page( form) like labels and values in the fields using a COPY button to capture all the information available on the page or form to copy that to system clipboard so that information can be used into another system.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved