Server Administration :: Free Space In Standby Database?
Jul 23, 2011How do we find the free space in tablespaces in a standby database
View 4 Replies
ADVERTISEMENT
Server Administration :: How To Know Which Block Contains Free Space
Feb 26, 2013for some reason,i want to know which data block contains free space,or which table/index contains free space.
View 6 Replies View RelatedAny Way To Free Space From Sysaux Table Space?
Oct 3, 2012Just 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?
Server Administration :: IPPS Has Use Up This Amount Of Space In Database From All Objects?
Oct 3, 2011select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
Server Administration :: Disk Space When Add Data File On Primary Database?
Jun 24, 2013I'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
Server Administration :: Change Space Allocation For Character Columns In Database
Oct 24, 2012I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?
Server Administration :: Create Controlfile In Standby Database
Jan 5, 2011we are having physical standby database and production database..we have create a soft link for a datafile in production to another mount point
ex- /disk1/data1 to /disk2/data1
now we want to keep the datafile in /disk2/data1 and remove the softlink..As we are having physical standby database . i need to know what are step to be taken. my action plan is as follows:
In production.
1. synchronise both standby(apply archive logs) and production.
2.shut down the standby
3.create controlfile in production in ascii format and edit the path (/disk1/data1 to /disk2/data1)
4. run the @control.sql that i have created
4.create a standby control file in production .
In standby:
1. take the backup of controlfile.
2.place the newly create controlfile(from production) to the location of controlfile
3. remove soft link.
4.startup mount.
5. then we can apply logs as usual to make the standby sync.
whether my action plan is 100% correct or any changes to be done.
Server Administration :: Manual Standby Database On Heterogeneous Platform
Apr 24, 2012We have a 2 node RAC installation on IBM AIX 5.3 with Oracle Standard Edition. We need to create a manual standby database (becoz Standard Edition does not allow Dataguard confign). Can we do this on a different platform (eg: Linux/Windows). This is to make use of existing servers for the standby environment.
View 1 Replies View RelatedHow To Get Free Space On Operating System Drive
Nov 5, 2013is it possible to get information about free space on drive/path for example with some DBMS_*, UTL_* package or with stored function based on Java
(e.g. CREATE OR REPLACE FUNCTION GetFreeSpace(driveLetter IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'DiskSpace.getFreeBytes(java.lang.String) return java.lang.String') but for JDK 1.3
Server Administration :: How To Reclaim Temp Free Blocks
Jul 26, 2011we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426
1 row selected.
further when i checked the session details using TEMP segment, i got below output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK#
explain what is the exact meaning of this column ?
to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;
Server Administration :: Check Free Extents In Tablespace
Apr 29, 2012which data dictionary view would you query to see the free extents in a tablespace.
i use dba_free_space;
SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200
Partition Drop Not Increasing Free Space In Dba_free_spaces?
Jan 16, 2013I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.
Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.
select sum(bytes / (1024 * 1024 * 1024)) "Size (GB)" from dba_free_space where tablespace_name ='&tbs_name'
I am using following command to drop partition
ALTER TABLE table_name
DROP PARTITION "partition_name"
UPDATE GLOBAL INDEXES;
What should be done to increase free space shown for tablespace after partition drop?
RAC/ASM Clusterware Installation :: Checking Free Disk Space On OS And ASM Bug?
Jul 9, 2012I've installed GI 11.2.0.3 on AIX 7.1.And today I noticed strange ASM's behavior:
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*2285768*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *188616* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
I guessed that was because of LUN size (it was exceed 2 TB)After that I dinamically shrinked LUN size on our external storage, rebooted and perfomed cfgmgr command on both nodes. But I still have no enough free space.
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*1992294*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *1992294* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
Active Trace Files Deleted - How To Free Up Space
Oct 11, 2013Some trace files were deleted with rm command. Obviously they were active and now the free space does not free up (over 9GB). How can this be fixed without database interruption?
View 4 Replies View RelatedServer Administration :: Shared Pool Free Percentage Greater Than 100
Jul 5, 2010value of "Shared Pool Free %" in v$sysmetric is larger than 100%
SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %';
VALUE GROUP_ID
186.45107 2
186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE
-------------- ------------ --
shared_pool_reserved_size big integer 832149913
shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes
Fixed Size 2163880 bytes
Variable Size 8.1068E+10 bytes
Database Buffers 2.0401E+10 bytes
Redo Buffers 79310848 bytes
Server Administration :: How To Free Up Tablespace Size When It Reaches Max Limit
Feb 15, 2011How to free up the tablespace size when it reaches its threshold (Max limit)?
Say for example USERS & SYSAUX tablespace reaches above 90%.
SQL & PL/SQL :: Delete Bulk Records In Tables From Which Path Can See How Much Space Is Free
May 5, 2010Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
For eg.
CREATE TABLE MLOG$_TEST
(
ID VARCHAR2(64 BYTE),
SNAPTIME$$ DATE,
DMLTYPE$$ VARCHAR2(1 BYTE),
OLD_NEW$$ VARCHAR2(1 BYTE),
CHANGE_VECTOR$$ RAW(255)
)
So is MLOG$_TEST oracle's internal table.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
TimesTen In-Memory :: Error 802 / 6220 / Permanent Data Partition Free Space Insufficient
Oct 29, 2012try to evaluate it as IMDB cache.I am facing this error repeatedly. I have increased perm size from 6.25 GB to 10 GB.After inserting about 460.000 rows I get the error again. Is it possible that 460.000 rows need 3.75 GB space?
In Oracle database these rows occupy about 200 MB space.
Server Administration :: RBS Tablespace Is Not Releasing Space
Aug 27, 2012In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Unfortunately still our environment is running in 9i due to application code
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 1800
undo_suppress_errors boolean FALSE
undo_tablespace
Server Administration :: Calculate Used Space In A Block
Nov 17, 2011Is there any way I can calculate percentage of space used in a block.Eg if a table size is 100 blocks,How Can I check the percentage of used space in block.
View 6 Replies View RelatedServer Administration :: Segment Space Management?
Oct 13, 2010 am using Oracle 10.2.0.4 on win 2008 server SP2. I would like to know if we can set the Segment Space Management feature to AUTO for RBS and Temporary tablespaces. As the data is not permanent in these tablespaces, will it manage automatically?
Presently its Segment Space Mgmt is manual for System, RBS, Temporary tablespaces.
Server Administration :: Managing Space In Drive E
Oct 27, 2010In my drive E: I'll have a space issue soon because one file is taking all space. SYSTEM01. DBF size is around 25GB. I want to know if I delete some data in my database I'll gain space.
View 2 Replies View RelatedServer Administration :: How To Reclaim Wasted Space Thoroughly
Mar 26, 2013In one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.
I have referenced
<Administrator Guide> - Reclaiming Wasted Space
[URL].....
I have several questions on reclaim space:
1. It seems that segment adviser give a really cool view to know which segment should be shrink and how much size will free up after shrink. But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :
A script from MOS, but actually I found this it's not very accurate with segment adviser. This script should report the real space the table occupy, but after shrink space, the space doesn't free up.
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Also there is a script evaluate the tablespace fragments from some people:
SELECT tablespace_name,
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1;
if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.
From <Concepts>:
Quote:Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more
extents.
2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)
Server Administration :: Verify Tablespace Space
Jan 9, 2012Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?
View 5 Replies View RelatedServer Administration :: Pre-allocate Space For Temporary Tablespace?
Jan 4, 2012I just want to know whether we have to pre-allocate space for temporary tablespaces?
View 1 Replies View RelatedServer Administration :: How To Delete Flashback Log And Release Space
Feb 15, 2012How to delete flashback log and to release the space?
I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
Server Administration :: System And Users Table Space
Oct 15, 2011a newbie dba here..
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/
result output is attached .txt file.
the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?
Server Administration :: Recover Space After Index Rebuild?
Apr 26, 2011We have separate tablespaces for Tables and Indexes. Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes. The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system. I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.
How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.
Server Administration :: Segment Space Management Is Auto?
Oct 18, 2010the syntax to make a tablespace's segment space mnagement auto which is segement management is *manual* and exent management is *local autoallocate*.
View 2 Replies View RelatedServer Administration :: Added Lots Of Space To A Tablespace
Sep 6, 2011Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error
ORA-03214: File Size specified is smaller than minimum required.
How to resolve this problem to reclain the space back?