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


ADVERTISEMENT

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

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

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

Setting Threshold Value Without Using Grid

Jun 13, 2012

if I create manual database how to set threshold value? is there any other tool to set threshold value?

View 4 Replies View Related

Email Notification To DBA When Threshold Of 80% Reached For Archive Log?

Jul 10, 2013

 11.2.0.3 I want the database to email me when the threshold of 80% is reached for the archivelog and backup piece at recovery_dest folder how to do this. Is this the dict table  I will check for threshold? 

select * from v$recovery_area_usage;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE                          0 0               0 REDO LOG                              0 0               0 ARCHIVED LOG                       2.02 1.06              21 BACKUP PIECE                       4.76 0              11 IMAGE COPY                            0 0               0 FLASHBACK LOG                         0 0               0 FOREIGN ARCHIVED LOG                  0 0               0  7 rows selected.

View 11 Replies View Related

SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters

Jul 1, 2013

I want to replace double space with single space and also remove junk characters from the data. How can I do that?

CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"

View 8 Replies View Related

Any Way To Free Space From Sysaux Table Space?

Oct 3, 2012

Just now sysaux resized to 600m from 250m >>

Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.

CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024)  from dba_data_files where tablespace_name = 'SYSAUX';

TABLESPACE_NAME       FILE_NAME                                             BYTES/(1024*1024)     AUT         MAXBYTES/(1024*1024)
SYSAUX                  /u01/app/oracle/oradata/test/sysaux01.dbf           600                  YES                 32767.9844
CODEsql> @SCRIPT.SQ

TABLESPACE   TOTAL_SPACE(MB)    USED_SPACE(MB)   FREE_SPACE(MB)     % Used    % Free
SYSAUX          600                  248            352               41.33        58.67

1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?

View 9 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

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

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

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

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

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