PL/SQL :: Reduce Rows In A Big Table

Dec 19, 2012

I have one very big table , says employee table, I need to keep less rows to do this

1. create table employee_backup as select * from employee

2. truncate table employee

3. insert into employee select * from employee_backup where rownum < 1000

is #3 sript is OK? if I only want 1000 rows ?

View 5 Replies


ADVERTISEMENT

How To Reduce Size When Table Is Null - Sqlplus

May 17, 2012

how can I reduce the size of ------------- when table is null. I m in sqlplus I typeSelect A,B,C,D,F,G,H from SOMEHERE where B='GOAT1';

if A is 10 char long
B is 50
c is 10
d is 30
e is 10
f is 50

if any of those don't have data it still outputs ----------------------------- (50) for B and tht covers the whole screenhow can I make is to show less if it null

View 2 Replies View Related

Server Administration :: How To Reduce Size Of A Table

Oct 2, 2012

One of our solaris machines is running Oracle 8.0.3

A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.

The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.

I would like to know the best procedure to delete the information and reduce the size of the file.

View 3 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

XE :: How To Reduce Space Of DBF

Apr 14, 2013

i have data about 3 gb but my files in e:oraclexeapporacleoradataXE has grown to 16 gb

1)my e: drive has less space ,pls tel how can i fee some space and give it back to OS

2)is there any other place where i can free up some space from oracle and give it back to OS.

-----------------------
windows (2008)
---
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE     11.2.0.2.0     Production"
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production (2008)
NLSRTL Version 11.2.0.2.0 - Production

[code]....

View 3 Replies View Related

Way To Reduce Dblink Timeout

Dec 28, 2011

is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.

View 2 Replies View Related

Forms :: How To Reduce Quantity

Dec 18, 2012

how to reduce quantity from one table due to other table for example .i have a purchase a shampoo in 20 quantity and then sale this shampo 2 quantity...so i want now there should be 18 quantity of shampoo in purchase table automatically

Purchase tableSale tabel
Product IdSale id
Product Nameproduct name
QuantityQuantity

View 4 Replies View Related

Reduce The Size Of Database

May 9, 2013

We have Oracle 10G database over Unix platform, Customer want to reduce the size of database as much as possible and the ami of customer to move the storage area of this database to other one. so we resize some datafiles and get lots of free space at mount point but while checking the utilzation of table is showing some what different as other. Below O/P:

                                                                                                                       %
                                                                                    %                         MaxPoss    Max
Tablespace Name                         KBytes            Used            Free   Used         Largest          Kbytes   Used
------------------------------ --------------- --------------- --------------- ------ --------------- --------------- ------
*a DATA                             45,875,200       8,740,992      37,134,208   19.1       1,728,512     100,663,248   45.6
*a HIGH_S_DATA                      21,504,000       1,331,520      20,172,480    6.2       3,048,704               0     .0
*a HIGH_S_IND                       15,360,000         853,568      14,506,432    5.6       1,661,504               0     .0

[Code]....

above all o/p is different, no able to understand it. is there any way to reset the HWM at Datafile level and how we reset the HWM of those tables having Materlized view?

View 18 Replies View Related

Oracle 8i Best Method To Reduce DB Size?

Apr 3, 2008

i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.

I've looked at shrink etc methods but some are not compatible with 8i.

I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.

View 1 Replies View Related

Performance Tuning :: How To Reduce Clustering_factor Value

Jun 7, 2012

how to reduce the clustering_factor's value which appears in the user_indexes view?

in my table ,its clustering factor's value is so high:

SQL> SELECT UI.clustering_factor,UI.num_rows,UI.index_type,UI.distinct_keys FROM USER_INDEXES UI WHERE UI.table_name = 'TAWB_AWB';

CLUSTERING_FACTOR NUM_ROWS INDEX_TYPE DISTINCT_KEYS
----------------- ---------- --------------------------- -------------
83609 187603 NORMAL 187603

and its block numbers is 5063

SQL> SELECT COUNT(DISTINCT DBMS_ROWID.rowid_block_number(ROWID)) BLOCK_NUM FROM TAWB_AWB A;
BLOCK_NUM
----------
5063

View 10 Replies View Related

Performance Tuning :: How To Reduce Consistent Gets Of SQL

Jun 27, 2011

How can I reduce the consistent gets of the sql:

select b.OFFER_ID,
b.OFFER_CODE,
b.OFFER_NAME,
b.OFFER_COMMENTS,
b.BAND_ID,
b.CAN_BE_BUY_ALONE,
b.PRICING_PLAN_ID,
b.PRIORITY,
b.STATE,
[code]...

I have already added the indexes for the outer sql to reduce the cost from 374 to 150. But the the consistent gets seems not reduce that much. I notice that the function will cost 6 consistents gets per execution. It seems it not very high. But if this function goes with the sql, it will cost a very high consistent gets, if I remove this function, the sql only cost 1903 consistents gets.

So what I am thinking there should be two ways to reduce the consistent gets..The first one is reduce the recursive call of the sql. The second is reduce the consistent gets of the function. (but it seems that the consistent gets in this function is very low, only 6.)

View 39 Replies View Related

SQL & PL/SQL :: How To Reduce Time Taken For Inserting Records

Jun 29, 2012

I have a view SV (say) which holds approximately 33,000 records. But, when I try to insert these many records into a table SV_T (say) it is taking huge amount of time i.e. 2-3 hrs (approx.)

how reduce the time taken for the insert.

View 3 Replies View Related

Performance Tuning :: How To Reduce Dml Locks

May 24, 2012

After ran db health check, my database report gives the following details

dml_locks OK. dml_locks = 3396, transactions = 849.

View 6 Replies View Related

How To Reduce TEMP Space In Oracle 11g

May 22, 2013

In 11g database i have allocated 30 GB temp space now am seeing only 500 MB free space in database.

how to reduse temp space.

View 5 Replies View Related

PL/SQL :: Space Required To Reduce Threshold?

Jul 3, 2013

There are tablespaces where the current usage is crossed 90% and we are getting alerts.I want to know how much space required to add for these tablespaces ( datafiles) so that  current usage can be reduced to 80 %. or 70 % Is the any SQL query to find this information . oracle 10 g database.  . 

View 5 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

Getting Error To Reduce Data File Size

Sep 1, 2010

I am getting the following error when i try to re size the data file.But the data file is having lot of free space.

ORA-03297: file contains used data beyond requested RESIZE value

Presently table space size is 220GB with 8 data files.As the process of the performance tuning we moved data to different table spaces.Now used space of the tablespace is 90GB.So I am trying to resize the datafile but it throw an error.

View 1 Replies View Related

Performance Tuning :: How To Reduce Cost Of Query

Mar 21, 2013

I have Following wjich takes some minutes to executes i want to be tune so this query Executes fast.

Query :

SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-

[Code]...

Explain Plan Result :

Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION

[Code].....

After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..

This table MG_COLL_SCP_DTL have index like this

CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)

[Code]....

How to Reduce cost ???

View 4 Replies View Related

Using Rownum In PL/SQL Can Significantly Reduce Performance And Throughput Of Queries

Nov 27, 2010

Using rownum in PL/SQL can significantly reduce performance and throughput of queries.

For example,

CODEselect *
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...)
where rownum < 500

takes much more time on a heavy loaded db than

CODEselect Y.*
from (
select X.*, rank() over(order by ...) rnk
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...) X) Y
where rnk < 500

I suspect it's because Oracle optimizer goals all_rows and first_rows.

View 2 Replies View Related

Server Administration :: Reduce The System Tablespace Size?

Aug 6, 2011

how to reduce the system tablespace size

my system01.dbf size is 6gb

i want reduce from 6gb to 2gb

View 3 Replies View Related

RAC & Failsafe :: RMAN Command To Reduce Space Usage

Sep 21, 2010

How to reduce the space usage in RMAN commands.

View 3 Replies View Related

Enterprise Manager :: How To Reduce The Dump Area Size

Jan 31, 2010

i just now installed oracle and import some schema to this new DB. but in OEM showing 40 % full of dump area

how to reduce the dump area size?

View 4 Replies View Related

Server Administration :: To Reduce Number Of Datafiles In Tablespace

Feb 25, 2012

We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .

View 1 Replies View Related

Performance Tuning :: How To Reduce Index Creation Time

Aug 17, 2010

Is there any way to reduce the index creation time. I have one table which has 7700000 records and every day this table get truncate and we create with create table as select statement and then create the 4 indexes and each index took 5 minutes so in totality it took 20 minutes in index creation.

View 2 Replies View Related

Performance Tuning :: WINDOW SORT - Reduce Time?

Nov 30, 2011

In my insert query, Window sort takes longer time i.e. 93% of total execution time, How do i reduce this time? are there any tuning parameters availabe for this?

View 5 Replies View Related

RMAN :: How To Reduce Full Database Backup Time

Feb 15, 2013

I am running full database backup it take 10hrs to complete.

Size of the database=590GB

Platform:Solaris 9

database version:10.2.0.5

using catalog database.

Is there any way to reduce the time ?

Is there any query to see how long does the backup runs?Monitor the backup time?

View 5 Replies View Related

Bulk Collect Cursor That Can Reduce Number Of Context Switches

Oct 4, 2008

Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.

View 1 Replies View Related

Windows :: Prepare Tablespace Growth Report For 106 DB - Reduce Manual Activity

Jun 29, 2011

I need tablespace name,tablespace total size and used size of every tablespace for my 106 databases. bcz I am planning to prepare tablespace growth report for my 106 dbs. If I have the 2 months data from now in xl sheet, then I can predict the growth rate of every tablespace accurate almost.

it is not a easy task to fill the space detail manually in xl sheet everyday.Hence I need some mechanism to do this activity automatically.

I know we can collect this detail in a csv file every day while running the script. but is there any mechanism to store these detail in a particular xl sheet one by one column automatically?

View 13 Replies View Related







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