PL/SQL :: Truncating Or Drop Table Is Faster?
Jul 8, 2012I have a table with 80 million records. Is dropping this large table is faster or truncating is faster?
View 6 Replies
ADVERTISEMENT
SQL & PL/SQL :: Truncating Table In Oracle 10g - ORA-00054?
Nov 7, 2012I would like to resolve my problem about truncating table in oracle 10g, i have a table with more than 2 millions of data and i tr to delete data using delete statement but it take many time and i tried with truncate it's ok but when i want to insert data into the table it give me this error ORA-00054 ORA-00054: ressource occupée et acquisition avec NOWAIT
View 30 Replies View RelatedSQL & PL/SQL :: IDs Decimal Values Are Truncating
Jul 10, 2012If I perform a Query as below
SELECT SUM(total_amt) FROM Amont_dtls WHERE id=10;
It's giving me the value as
2.08166817117217E-19
The values in total_amount column for ID=10 is 0.01 and -0.01..Actually it should return 0.If I use the query like
SELECT SUM(0.01-0.01) FROm Amont_dtls WHERE id=10;
Then I am getting 0.Why it's a variation.And also the data type of the column is NUMBER.If I use
SELECT TRUNC(SUM(total_amt)) FROm Amont_dtls WHERE id=10;
Then I am getting 0.But some other ID's decimal values are truncating.
Ex: If i Use TRUNC
Instead of 28781947.48 it's showing 28781947
Instead of 590874.5 it's showing 590874
SQL & PL/SQL :: How To Release Storage When Truncating Partition
May 27, 2012assuption is that when we truncate the partition it immediatly release the allocatd storage.i have just tested the scenario , but still i can see that table size is same even after truncating the partition( which have around 25% of the data)
alter table test truncate partition t1p3 update indexes ;
OR
alter table test truncate partition t1p3 drop storage update indexes ; to see the table size:
SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg
FROM (SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
[code]...
SQL & PL/SQL :: Http Output Text Truncating?
Oct 12, 2011I have a requirement to capture a large number character string which is an output from http request. I created a CLOB datatype to capture it. I see an issue of truncating of the characters after 1998 character's (Block 1, below). If i assign the same ouput put and assign to variable of CLOB type and displaying 3794 Character as required(Block 2) which i am expecting the same in Block 1 too to see entire string ....
For your reference pl copy below link and past in explorer to see th entire output put.
********
http://gissms-stg-05:8399/arcgis/rest/services/ESRI_Route_NA_SA_CF/NAServer/Service%20Area/solveServiceArea?facilities=-95.452487%2C2 9.789048&barriers=&polylineBarriers=&polygonBarriers=&defaultBreaks=1&travelDirection=esriNATravelDirectionFromFa cility&outputGeometryPrecision=&outputGeometryPrecisionUnits=esriDecimalDegrees&f=json
**************************************************************
BLOCK : 1
declare
l_text3 CLOB;
begin
[code]...
Truncate / Drop Partitions In Table Having Nested Table Columns
Sep 7, 2012I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
SQL & PL/SQL :: Drop A Table?
Apr 6, 2010When drop a table what happen to view,sequence,synonyms,index,constraint for the table
View 9 Replies View RelatedWhy A Certain Date Makes Query Faster
May 25, 2012I have a query with a few tables in join, and I filter data with something like
where mytable.initial_date > sysdate-30
If I use any date, it takes about 5 seconds to run, but if I use
to_date('01010001','ddmmrrrr')
instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?
Index On Oracle 11g - Get Results Faster?
Nov 9, 2010i have table (MEN) with 900,000 records.in this table i have field `IP` and `Tdate`.when i run query:
select * from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')
it takes long time until i get Result.i try to make index like this:
create index
my_in
on
MEN (IP,Tdate );
but how to run the query to get fast Result?i try this:
select My_in from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')
and get error: `ORA-00904`
RMAN :: Restore / Recovery From Snapshot Faster?
Aug 8, 2012Version : 11.2 on Solaris, AIX
All our production DBs are backed up by some Hitachi tool (apparently it is called ShadowImage) . Apparently they put all the tablespaces in Backup mode and 'take a snap' . Our BAU team says this is faster.
On an event of DB Crash Is it easier to restore, recover from these 'snapshots' ? Are these snapshots much faster than RMAN ?
Performance Tuning :: Query Sql Server Faster Than Oracle?
Feb 21, 2013I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.
View 1 Replies View RelatedPL/SQL :: Records Return Faster Inline Rather Than With Or Depends On Situation
Jan 15, 2013I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
[Code]....
SQL & PL/SQL :: Privilege - Just Drop Special One Table?
Jul 3, 2013There two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
SQL & PL/SQL :: Unable To Truncate / Drop Table
May 13, 2011I 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
SQL & PL/SQL :: Drop Table With Special Character?
Jul 15, 2011I have created a table with space by mistake.how to drop that..?
QL> select * from cat where table_name like 'OM_DW_RTNORD%';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE
[code]....
no rows selected
SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character
SQL & PL/SQL :: Drop Global Temporary Table?
Dec 6, 2011how to drop global temporary table?
while droping global temporary table we are getting below error
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"
SQL & PL/SQL :: How To Drop Queue Table From Database
Nov 26, 2011,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error
error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST
SQL & PL/SQL :: How To Undo A Drop Table Operation
Feb 24, 2013How do I perform Undo a drop table operation?
View 12 Replies View RelatedSQL & PL/SQL :: Drop Index Which Has Constraint On Table?
May 18, 2011How to drop an index which has a constraint on the table?
while droping the index on the table wheather i should remove the
constraint or disable the constraint or without droping the
constraint
Drop Table Response Time?
Jul 23, 2012I am working on 10.2.0.4 oracle version database of my production,
when executed a simple drop command , the total time it took is 26 secs. on Avg. the table is holding only 20 records. this is happening for the last few weeks , prior to that it took less than 0 secs.
05:22:58 SQL> drop table C$_100GL_INTERFACE ;
Table dropped.
Elapsed: 00:00:26.67
but on successive executions the elapsed time falls to 10-15secs.
And on the same test env, we are achieving the expected results less than 0 secs.
Server Utilities :: Faster Impdp Excluding Indexes And Constraints
May 24, 2012We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints
Steps
Import schema excluding constraints,rf_constraints,indexes with metadata only
Import schema with data only
spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes
execute on Destination the Index creation ddl
execute on Destination constraints and rf_constraints ddl
Drop Some Partitions In Table On Production Environment
Jul 8, 2011I have to drop some partitions in table on production environment (to get free space). The environment have to be continuously available. I was considering of use ALTER TABLE ... DROP PARTITION ... UPDATE INDEXES but it is slow, because of use clause UPDATE INDEXES. Is there another possibility to remove these data?
View 2 Replies View RelatedCompressed Table - Drop Unused Column
Feb 9, 2012what happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.
My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?
Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.
SQL & PL/SQL :: Create And Drop Table Inside The Procedure
Jun 10, 2013I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
Backup & Recovery :: Recover Drop Table
Dec 29, 2011How i can recover my drop table..i use user managed backup concept after dropping table i cant recover my table..how i use step that my dropped table recover.
View 12 Replies View RelatedDrop Statement On Table Taking Long Time
Sep 20, 2010We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
Server Administration :: How To Drop A Column In Huge Table
Aug 8, 2011I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?
SQL & PL/SQL :: Unable To Drop Interim Table After Redefinition Process
May 11, 2012I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.
And after that when I tried to drop the interim table , I am getting the below error message
"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"
So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?
Backup & Recovery :: RMAN - How To Recover Drop Table
Jan 3, 2012how i can recover my drop table using until time in rman scenario.
View 3 Replies View RelatedServer Administration :: Drop A Table Moved To Recycle Bin?
Jan 6, 2012I have a question about recyclebin.When i drop a table,the talbe will be moved to recyclebin,the name is changed to BIN$...,but the constraint built on the table aren't moved to recyclebin and their name are also BIN$...,why?
View 1 Replies View Related