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
ADVERTISEMENT
Server 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.
Automatic Segment Space Management?
Jul 30, 2013Version: 10.2, 11.2Platform : Unix, Linux flavours
Question1. From googling , I gather that ASSM ( Automatic Segment Space Management ) is a new method used by Oracle to manage space inside data block. If this is the case, then it should have been named 'Automatic Block Space management' . Right ?
Question2.What was the most notable advantage in your opinion in using ASSM as opposed Manual Segment space management ?
Manual And Automatic Segment Space Management (ASSM)?
Jan 5, 2012I have some table spaces manual and others automatic, i just want to know what's the recommended one, to change it to the best way.
How to change Segment Space Management of a tablespace from MANUAL to AUTO? in oracle 10g R2 and also want to know the main difference between Manaul and Automatic Segment Space Management.
Server Administration :: Space Management - Unable To Resize Datafile?
Feb 13, 2013I have one generic question about space management. I have one table with size of 1TB. This table stored in ORC1 tablespace. This tablespace contains 70 datafiles.
Since it's 10.2.0.4 database. I have dropped this table by using purge
drop table <<table_name>> purge;
Once table drop was completed. When I check the tablespace space it was 100% free but due to HWM was unable to resize the datafile from current size to small size. What was the reason behind this. Is there any process needs to follow when dropping big tables ? like instead of dropping the tables do I need to truncate first & then drop .
Server Administration :: Space Management In 10g / High Water Mark
Jun 10, 2013I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables. Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.
SQL> SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
2 round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
3 round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
4 5 6 FROM (
7 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
[code]...
Server Utilities :: ARGUMENT Segment Taken More Space Out Of Total System?
Jun 12, 2012After importing my dump, i have noticed that ARGUMENT$ segment taken more than 9 GB out of my total SYSTEM table space.I belive ARGUMENT$ table is used only to store procedure/package parameter details. But I am not sure Why it has taken more space.
Is there any way we can reduce the SYSTEM table space? using with the below details?
Import Details:
--------------
1) Imported using IMP DP. List of parameters used are userid, logfile, dumpfile, directory, job_name and remap_schema.
2) Dump file size is 3GB
3) The below list will be no. of objects imported using my dump.
OBJECT_TYPE COUNT(1)
------------------- ----------
DATABASE LINK 1
FUNCTION 246
INDEX 4742
[code]...
4) The below list will be amount of space occupied by the segments in the SYSTEM.
col owner form a5 word wrap
col segment_name form a15 word wrap
col segment_type form a15 word wrap
select owner,segment_name,segment_type
,bytes/(1024*1024) size_m
[code]...
Server Administration :: How Segment Different From Object
Dec 26, 2012Are the terms object and segment same ? what are the differences between them? what are similarities between them?
View 3 Replies View RelatedServer Administration :: Segment Size?
Dec 28, 2011I am working to understand the space allocation of table with the value we provided with the data type. For that I have created a table with varchar2 and length 50. Size of table created is of 65536 Bytes. This is when we don't have any insertion in the table. Later when we insert some rows, total size if the segment still remain same that is 65536 bytes.
Now again when I created table with varchar2 and length this time is 500 but still it is created with same size that is 65536. So can you just explain, on what values segment size depends on and how the length effect the size & space allocation.
db_block_size is 8192.
Server Administration :: ORA-10637 / The Segment Does Not Exist
Sep 26, 2011Does It can not shrink in manual space managment tablespace?
I was confused by the error ORA-10637: The segment does not exist
create tablespace test_manual
datafile 'd:oracleoradataorcl est_manual.dbf' size 100m
segment space management manual
create user test_man identified by hxl
default tablespace test_manual;
grant create session to test_man;
grant resource to test_man;
[code]....
Server Administration :: Rollback Segment Too Small ORA-01555
Jan 19, 2012One of the users received the error
ora-01555: rollback segment too small
I have read about the error and saw that it is caused by transactions made upon same data, filling to maximum one of the UNDOTBS rollback segments.It happens only once in a while, each time on a different Rollback Segment.
I've read that i should do a few things to enlarge those segments, such as bring a 1. segment offline, 2. drop it and then 3. create it with a bigger size & possibly a bigger extent setting
i've also read that those actions aren't relevant if you have the parameter UNDO_MANAGEMENT set on AUTO, which is actually the case.is that why when i execute ALTER ROLLBACK SEGMENT RB_SEG_NAME_11$ OFFLINE;
1. How do i solve my issue with the Rollback segment being too small to contain the snapshot with the requested SCN?
2. what does the parameter UNDO_MANAGEMENT mean? should i change it, in order to adjust my Rollback Segments attributes, to prevent my error of re-occurring?
Server Administration :: Block Corruption - Segment Not Found
May 16, 2012I have a database where view v$database_block_corruption shows a corrupted block
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 756652 1 5.5157E+12 CORRUPT
I am not able to get the relevant segment from the above information
SQL> select segment_name, segment_type, owner
2 from dba_extents
3 where file_id = 4
4 and 756652 between block_id
5 and block_id + blocks -1;
no rows selected
DBVERIFY Summary
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 3119107
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 755048
[code]....
I have uploaded the complete logfile.
Below is a part of logfile
DBVERIFY - Verification starting : FILE = /prd/dvp/ora/oradata/LHF/disk06/gds_t01_01.dbf
Block Checking: DBA = 21728172, Block Type = KTB-managed data block
**** kdxcoavs = -84 < 0, avail = 3129
---- end index block validation
Page 756652 failed with check code 6401
##not here that 756652 is the same block# mentioned in v$database_block_corruption
here i tried finding the OBJECT ID as below
SELECT dbms_utility.data_block_address_block(21728172) "BLOCK", dbms_utility.data_block_address_file(21728172) "FILE" FROM dual;
BLOCK FILE
---------- ----------
756652 5
Now for the same BLOCK it is giving different File, again the segment_name or segment_id could not be found from the above information.
Server Administration :: Deferred Segment Creation - Default True?
Jan 20, 2010What do you think of this 11.2 parameter? It can give some pretty weird results.
SQL> create tablespace ts1 datafile size 10m;
Tablespace created.
SQL> create table t1 (c1 date) tablespace ts1;
Table created.
SQL> drop tablespace ts1;
Tablespace dropped.
SQL> insert into t1 values(sysdate);
insert into t1 values(sysdate)
[code]...
Server Administration :: ORA-01652 - Unable To Extend Temp Segment By 128 In Tablespace DATA_01
Mar 10, 2012When i try to create a duplicate table from an existing table i get error.
SQL> create table COMP_TEMP as select * from COMPETITIVE;
create table COMP_TEMP as select * from COMPETITIVE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace DATA_01
The table size is 15 gb.
Currently the tablespace has 2GB free space. If i need to increase the size of the tablespace DATA_01,how much space is required.
Server Administration :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace?
Aug 11, 2011I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full
SQL> alter table LINUX_CATA.TST_FOLDERS move tablespace LINUX_CATA;
Table altered.
SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA
[code]....
Server Administration :: When Did A Datafile Auto-extend
Dec 21, 2010Is there a way to find out when a datafile for an undo tablespace with autoextend enabled actually extended? I've done a few tests, and nothing is written to the alert log or any trace file that I've found. I can't find any V$ or DBA view that will give me the history of a file's size.
View 5 Replies View RelatedServer Administration :: Get Script That Create Partition By Auto?
Oct 27, 2011Is there a script that it create partitions by auto? who can share it?
View 1 Replies View RelatedServer Administration :: Objects Invalid Due To Auto-compilation
Jun 27, 2012One of my friends is facing a peculiar problem where objects are getting "Invalid" during execution I suspect it is happening as they are changing system date during their testing (time travel) which can create conflicted last_ddl_time on objects having dependencies
Consider a scenario
[1] system date is 10-06-2012 there are total 10 objects which has status as 'valid'
[2] the system date is changed to 10-07-2012 Now out of 10 Only 5 objects are compiled During execution ORA-04065,ORA-06508, ORA-06512 are observed
[3] the system date is brought back to 10-06-2012 Again during execution ORA-04065,ORA-06508, ORA-06512 are observed
suppose in step 2 objects are compiled whereas there synonyms are compiled in step 1, only thus last_ddl_time for objects will be later to that of its' synonym...
Does database validate last_ddl_time for objects having dependency during execution and then auto-compiles or invalidates the objects?
Server Administration :: Monitoring Auto Allocate Tablespaces
Nov 7, 2011Currently all my tablesapces are "UNIFORM" and I am looking into going to "AUTOALLOCATE" so space will not be some much of an issue.
I am using the following code and here is the output
sqlplus -s / <<-EOT
set pages 50
set line 100
set verify off
set feed off
set trimspool on
[code]........
Have a script that can emulate my above output and also deal with tablespaces that can autotallocate?
Server Administration :: Automatic Memory Management
Apr 7, 2013I have read this article:
[URL].........
And i see:
Quote:
If MEMORY_TARGET is set to non zero value:
SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
So, I have set:
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;
alter system set memory_max_target=512M scope=spfile;
alter system set memory_target=300M scope=spfile;
, and then bounced the instance.
After startup, I see:
SQL> startup
ORACLE instance started.
Total System Global Area 272027648 bytes
Fixed Size 1384012 bytes
Variable Size 100663732 bytes
Database Buffers 163577856 bytes
Redo Buffers 6402048 bytes
Database mounted.
Database opened.
SQL>
But Total System Global Area should't be in that case 60% of memory mentioned in MEMORY_TARGET? Memory mentioned in MEMORY_TARGET was 300M, and 60% of 300M is 180M, which is not 272027648 bytes.
I just want to use automatic memory management, so I've set the other parameters above to 0. My instance is on my local machine, so just for my own personal use.
Server Administration :: Tablespace Auto-Extend OFF On Production System
Jun 6, 2012I want to OFF tablspace AUTOEXTEND on a prodution system, we have many RAC databses and that will be done on all stations. i have got a document from net which was written on 29-Jun-2007 and it says that if need to OFF the AUTOEXTEND of a TABLESPACE so you need to ist make it off on the underlying datafiles of that tablespace so this doc is for Oracle 8.1.7.2.0
View 2 Replies View RelatedBackup & Recovery :: Deferred Segment Management / RMAN Backup
Jul 28, 2011We all know about the new feature introduced in Oracle 11g ie. "Deferred Segment Space management" in which there in no segment allocated to table which is empty, which means that only the data is going to be inserted in the table the first segment is going to be allocated.
I created a user assigned a default tablespace to it, created 3 tables, two of them having data and 1 empty table.
When I did export using the export utility, I found that the empty table was not included however the same situation contradicted when I did the export using "EXPDP".
I want to know is the something of tables missing in export happen in RMAN backup of the tablespace which I have assigned to that user.
Server Administration :: How Oracle Implements Undo Management
Jul 31, 2010I have been reading various articles about the undo management. This basic concept of undo management is simple but how oracle implements it is bit harder for me to grasp.
What i have read and understood is that whenever a DML(Update, Delete, Insert) statement is issued by a user, the data is fetched from datafile to database buffer cache and at the same time a copy of the original data is saved in undo segment. Now if other users requests the same data, they are presented with the unchanged copy in the undo segment.
Now I have the following questions:
1) In case of Insert statement, what data is saved in undo segment. Is it the complete data in the table to which we want to insert the new row?
2)When the user issues DML statement, there are three copies of the same data, one in Memory (which is changed and not the same as original data), second in Undo segment (Which is unchanged copy of original data) and third in datafile file ( which is original data). What is the difference in the data in undo segment and data in datafile at this stage. Why are the other users presented with the data from undo segment rather than original data from the datafile to maintain read consistency.
3)When the user issues rollback, the changes made to the copy of data in memory are undone.The copies of data in memory and undo segment are now same?. What happens to the before change copy in undo segment. Is it still there or deleted.
How To Make System Table Space Extent Management Dictionary
Dec 21, 2010I want to create system table space's extent management dictionary with the syntax:
CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M
but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause
how can I make system tablespace's extent management dictionary?
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 :: 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 RelatedServer 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 Related