SQL & PL/SQL :: Truncate All Tables From Tablespace

Mar 10, 2011

I have two tablespace a and a_idx , a is comtaining all tables data and it fixed size is 19000MB and b containing all the index associated with these tables and its size also 19000MB.

but this is am testing database, and we need some space on my unix machine for this i truncate all tables from a tablespace a
and resize the tablespace a with size 1000M with no error.

but as per my understanding after truncating all tables from tablespace a its also release space from tablespace b but its not hapening.

so my questin is how can i reize tablespace B using alter database datafile 'full path of dbf file' resize 1000M.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Truncate For Selected Tables

Jun 8, 2012

i have many user tables in my database and i want to truncate selected tables and how i can overcome this scenario.

i can truncate using truncate table <table_name> but they are 250 tables to truncate, so,i can't write truncate command for every table. if i want to truncate first i have to truncate parent table or child table and how i can find parent table and child table for given tables to truncatein my database.

View 6 Replies View Related

SQL & PL/SQL :: Effect Of Truncate On Associated Indexes Of Tables?

Jul 21, 2012

I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.

Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.

View 1 Replies View Related

Performance Tuning :: Truncate To Some Of Tables

Jan 13, 2012

We have a procedure, which do truncate to some of the tables. Most of the time it finished in short of spam of time. But from last few days, it is taking much longer time.

where should i start the investigation.

View 4 Replies View Related

SQL & PL/SQL :: Separate Index And Tables In Two Different Tablespace

Apr 14, 2013

How to saperate inndexes and tables in two different tablespcae.......???

View 2 Replies View Related

Server Utilities :: Exp / Imp Tables From One Tablespace To Another?

Oct 19, 2012

C:UsersNeetesh>expdp system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2

Export: Release 11.2.0.1.0 - Production on Fri Oct 19 16:39:06 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/*****@orcl2 dumpfile=temporary.dmp tables=testuser.test,testuser.test2

[code]...

then i import it in new user

SQL> create user temp identified by temp;
User created.
SQL> grant create session ,create table to temp;
Grant succeeded.
SQL> alter user temp quota 10 m on users;

[code]...

it gives 2 errors both are same as -ORA-01950: no privileges on tablespace 'new_tbl'

exported table are exist in this 'new_tbl' tablespace but this is not exist in importing database.then is there any way to import these tables in 'users' tablespace or other tablespace other than 'new_tbl'?

View 2 Replies View Related

Performance Tuning :: Moving Tables To Encrypted Tablespace?

Aug 25, 2013

i am using 11.2.0.3.0 version of oracle. We are planning to move some ~40 tables/indexes to new encrypted tablespace as a part of TDE(transparent data encryption). Currently three tables are having size ~30GB and one having ~800GB other have <2GB in size. And tables/indexes are altogether placed in different tablespaces.

whether i should create as many no of encrypted table spaces as it was before as unencrypted tablespace? or I should create one encrypted tablespace and move all the tables/indexes into that?

View 2 Replies View Related

Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables

Aug 16, 2012

We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space

SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;

which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?

View 18 Replies View Related

Server Utilities :: How To Import Dump Into Specific Tablespace Instead Of Default Tablespace Users

Jan 18, 2012

How to import dump into specific tablespace instead of default tablespace users.

I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .

View 2 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?

creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

My os version is

Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux

My database is OLP system.

My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?

Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.

View 7 Replies View Related

SQL & PL/SQL :: Truncate Vs Delete?

Apr 9, 2010

i have one question that why oracle not set high water mark when we delete data from a table and commit it, on the other hand it set in case of truncate.both of these statement release physical structure(in case of delete after commiting)

View 7 Replies View Related

Truncate Partition From PL/SQL Package

May 8, 2013

I have an Oracle Package with a procedure in which

package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .

The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.

Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.

Don't understand why and which privileges must give to the user to run the procedure from inside the package.

View 3 Replies View Related

SQL & PL/SQL :: Is Truncate Data Recovered

Jan 19, 2012

Is it possible to recover a table which is Truncated

View 13 Replies View Related

SQL & PL/SQL :: How To Truncate Specific Sub Partition

Dec 3, 2010

I am using oracle11g. I want to truncate subpartition on specific partion.

I have partition on statewise. Each state partion has 7 day sub partition.

For intance,

Partion TX
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN

Partion CA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN

Partion IA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN

Now i want to perform following tasks.

1. Need to truncate TUE sub partiion on TX partition.
2. Need to truncate WED sub partiion on CA partition.
3. Need to truncate SUN sub partiion on IA partition.

How do we do this?

The below statment truncate all TUE partition on all the partitions.

ALTER TABLE TRX_TABLE
TRUNCATE SUBPARTITION TUE;

How do i tuncate specfic sub partition on specific partition?

View 4 Replies View Related

SQL & PL/SQL :: Truncate Master Table?

May 17, 2013

The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.

1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully..
2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table)
3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table

View 15 Replies View Related

SQL & PL/SQL :: TRUNCATE Can Rollback Data?

Jun 4, 2013

I have found one interview question no.7 @ [URL] According to that it can be rollbackQuote:The truncate command is a DDL operation and just moves the high water mark and produces few rollback data.

But, I read That is not true in the case of oracle but it is true in the case of SQL Server.

View 7 Replies View Related

SQL & PL/SQL :: Get Coding Right To Truncate Old Entries?

Jun 15, 2010

currently developing a app for MSM in tromsø, that are going to delete the oldest entrys in the database table and then update all the relative Run history entrys in another table.

And i can't get the coding right to truncate the old entrys.Here are the

DECLARE V_SLETT varchar2(125);
V_NYE varchar2(50);
V_SLETT := :SLETT;
V_NYE := :NYESTE;
truncate DELTAKER ( IDNR in (V_SLETT) );
begin
request := UPDATE_RUN;
end;

View 15 Replies View Related

PL/SQL :: How To Convert / Truncate Date

May 30, 2013

How to convert/trunc date

Example
source
column name CONFIRMED_DTE
16-APR-09 12.00.00.000000000 AM

target
column name CONFIRMED_DTE
16-APR-09

I want to date like 16-APR-09 from 16-APR-09 12.00.00.000000000 AM. In a columns have null values and 16-APR-09 in source and target

View 8 Replies View Related

Truncate Table Before Import

Aug 16, 2012

i need to import into table A. Before that i will do a truncate.

I will disable the constraint.

DO i need to disable any triggers on the table before importing?

View 12 Replies View Related

SQL & PL/SQL :: Truncate Partition Using Syntax

Oct 10, 2013

I am trying to truncate a partition using syntax .ALTER TABLE SALES6 TRUNCATE PARTITION FOR(DATE '02-03-07')

but when i query back to table i can still see the data it is not truncating.

create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
[code]....

View 12 Replies View Related

SQL & PL/SQL :: How A Truncate Command Comes Under DDL Section

Mar 2, 2011

How a truncate command comes under DDL section, instead DML. All the operations happening inside the object is called DML and ON the object is called DDL.

View 7 Replies View Related

SQL & PL/SQL :: Unable To Truncate / Drop Table

May 13, 2011

I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.

I am using Oracle 11g Release 11.1.0.7.0

View 17 Replies View Related

SQL & PL/SQL :: Truncate Partition - Resource Busy?

Sep 1, 2010

i have one procedure that run at same time for two different partition.In the begin of the processes i do the truncate partition and after that the insert of data in the partition.Since i have two processes running at the same time, i will fill up data in two different partition.

But it occurred one error :
"ORA-00054: resource busy and acquire with NOWAIT specified"

The problem is caused by the truncate partition. I'm doing two simultaneous execution of the same process, but when one process is doing the truncate other is insert data.How can i do the truncate of this two partition, or insert in to, without get any error. I think the two simultaneous insert is not a problem.

So how can i lock the table in the first truncate and after that release the table for the next truncate?

View 5 Replies View Related

SQL & PL/SQL :: Logical Difference Between Delete And Truncate?

May 7, 2012

when we try to truncate the parent table data although its child tables are empty, the oracle oracle throws an error--"ORA-02292: integrity constraint test_id_constr violated - child record found" , but when we delete this parent table data by "delete" statement then it gets deleted. what is the logic behind "truncate".

View 3 Replies View Related

SQL & PL/SQL :: Truncate Partition Based On Given Input?

Jun 20, 2013

How to truncate the partition based on the given input. I've below table partitioned on dt field. How can i truncate the whole partition which contain the date 2013/04/30.

CREATE TABLE TST_SUMMARY
(
CUST_ID NUMBER(38) NOT NULL ,

[Code]....

View 21 Replies View Related

PL/SQL :: Finding The Maximum Truncate (date)

Jul 31, 2012

I've the following data set

with t as
(
select 1 cor_id , 'SR' rt_cd,TO_Date( '05/12/2010 01:12:19 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 2 cor_id , 'SL' rt_cd,TO_Date( '05/12/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 3 cor_id , 'SR' rt_cd,TO_Date( '04/10/2010 02:11:15 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 4 cor_id , 'SL' rt_cd,TO_Date( '04/10/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual
)
select t1.*
from t t1for each iss_id =100 ,

there could me more than one record in the above data set, The requirement is i need to get the records where the trunc(dt) is maximum

I need to get 2 records with cor_id= 1 and 2.

how can i get those records

View 3 Replies View Related

How To Grant Truncate Any Table To A User

Jan 30, 2013

on 11G R2,

We want to grant truncate any table to a user.

How ?

We should create the following procedure ?

1. Create the procedure to truncate the table.

create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)

[Code]....

grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?

View 6 Replies View Related

PL/SQL :: Why TRUNCATE Command Would Not Work In Block

Sep 10, 2012

Why the TRUNCATE command would not work in PL/SQL block ? A work arround would be dynamic SQL, but I'm avoiding it...

begin
truncate table test;
end;

View 7 Replies View Related

PL/SQL :: How To Truncate Data In Table Partitions

Aug 9, 2013

I couldn't either DROP or TRUNCATE the table partitions that were created. Here are the DDLs and DMLs I'm using. 

Create table student(no number(2),name varchar(2)) partition by range(no) (partition 
p1 values less than(10), partition p2 values less than(20), partition p3 values less     
than(30),partition p4 values less than(40)); 
Insert into student values(1,'a');
Insert into student values(11,'b');
Insert into student values(21,'c');
Insert into student values(31,'d'); 

When I do the following query, it returns data.

SELECT * FROM STUDENT PARTITION(p1); 

But, when I try to perform any of the following queries, it says invalid partition name. 

ALTER TABLE STUDENT DROP PARTITION p4;
ALTER TABLE STUDENT TRUNCATE PARTITION p3;

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 9 Replies View Related







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