I am having an issue with the LOBs in our Database. Our production DB is about 71G right now - if i do export/import using the DataPump - I see the data volume becomes 40G. I am sure its because of the LOBs in my Database. Our application uses lots of LOB datatype and I think the space is not being released.
The challenge is I cant do a full export/import in my Production DB as its a 24/7 system and to do export/import I need min. 5 hrs - which my business will not permit. I can I claim the unused space here. I have used Segment Advisor - but it has only given me 6G of space gain.
Is there any script I can try to use to reclaim space in the database.
I am trying to use shrink to reclaim space on some tables:
-First of all i am using this query to verify which tables are eligible to shrink: select segment_owner, segment_name, segment_type, trunc(((allocated_space)/1024)/1024) "aloc", trunc(((used_space)/1024)/1024) "used" , trunc(((reclaimable_space)/1024)/1024) "reclaim" from table(DBMS_SPACE.ASA_RECOMMENDATIONS()) where segment_owner='&owner' and segment_type='TABLE' order by reclaimable_space desc;
In 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)
i have a tablespace with a datafile of 20g. now by mistake i delete the datafile and then try to delete the tablespace from EM but i got an error which says that data file is not present to delete
Now initially after deleting the file physically so then i check space by applying df -ah at os lvl so it didn't reclaim the space now i try to delete the tablespace from em so it gives me the above error. This might be due to tablespace existence. so how can i reclaim the space.
lv_ret := WEBUTIL_FILE_TRANSFER.AS_To_Client_with_progress(lv_clnt_file, lv_srvr_file, 'Download from Application Server in progress', 'Please wait');
to download a file to my H: drive.Here lv_ret is a boolean variable.The file is not downloaded to my H drive when there is no enough space.How to capture that error?
There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
My organisation is currently discussing different storage options for the database storage. Our production database is nearly 2TB and we do not want to continue with the existing NetApp storage (we use a 2 node RAC running 11.2.02 with nfs filesystem from NetApp filer).
We were looking at different options and came across Nimble Storage, they are very fast growing company aiming mid-range storage customers. The initial talks and demonstration looked very promising in terms of IO performance (they claim 40,000 - 60,000 IOPs for their CS400 series Nimble Storage array) and other options they are providing but we understand that majority of their customers are using it for VDI and other infrastructures.
They have demonstrated us using if for Oracle database with ASM storage over iSCSI LUNs. We are yet to do the POCs and benchmarking.
Has anyone come across Nimble Storage for running Oracle databases?
we 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 ?
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;
We are maintaining a DR of our Database Server(oracle 10g R2 atop SUSE SP1 Linux) using Platespin(
[URL]......
Platespin is set to replicate(block based), incremental data(delta) every 1.5 hour from Production to DR site over a 30 Mbps dedicated fiber link.
Our maximum changes of data per day(during business hours) wont exceed 300 MB. During business hours Platespin replicates at least 1 GB at every replication cycle, while during off hours it replicates 300 to 500 MB per replication cycle. We are facing this strange issue with this box only(SLES 10 SP1 + Oracle 10g R2), we have protected MS Exchange 2007 Server based workloads without this strange issue, i.e in case of Exchange only delta replicates from Production server to DR site on Platespin.
Platespin support says us that Oracle re-indexes its database for better performance, so it is possible that re-indexing causes the blocks level changes on the storage, and since Platespin works on Block level, thats why it replicates so much(even though data is not changed that much)
here is actual words of Platespin support
<snip>
I think whenever Oracle database Indexing happens, it changes almost most of the Blocks of database and Platespin replicate all those Blocks.
As you know, Platespin checks the Date/Time attribute of every blocks before replication and if Date/Time attribute changes from last replication, it considers as changed block and replicate those blocks on Platespin Appliance. So, my suggestion is just look into the Oracle server behaviour before/after Data indexing process and do needful or do some workaround to overcome this issue.
I have a question about Oracle schemas. Oracle schema is the user with its datase objects (table, index, eg) .In two different Oracle schemas, there can be two tables with same names. When the users connect to same Oracle instance they can access these tables with schema.tablename convention.
However, how is that structure handled in physical database storage. Are there two tables created with same name physically? I mean are these two tables with same name stored in different database files?
Sun Solaris 10, Oracle 11gr2 x86 How are you doing? I am trying to build a test system. I never build ASM before. if I need to install database first then Migrating to ASM? The other problem I am having is that I only have a Raw disk. I have one disk for root and oracle and the other one for Raw disk for ASM. Do you know it's possible to build ASM on 1 raw disk?
We have standalone database running on ASM. Its 11Gr2 linux version5 server. After the Database bounce, the DB isnt coming up and is showing the below error.
SQL> startup nomount ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/test/spfiletest.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/test/spfiletest.ora ORA-15056: additional error message ORA-17503: ksfdopn:2 Failed to open file +DATA/test/spfiletest.ora ORA-15001: diskgroup "DATA" does not exist or is not mounted ORA-00450: background process 'ASMB' did not start ORA-00443: background process "ASMB" did not start ORA-06512: at line 4
Also i checked the ASM disk groups. I can see all those are MOUNTED properly. In fact i could also see the spfile present in ASM disk physically. It looks like it couldn't identify the spfile to start up the db. however i could see it physically present in ASM disk group. Find below snapshot.
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 358400 329103 0 329103 0 N DATA/ MOUNTED EXTERN N 512 4096 4194304 358368 358288 0 358288 0 N FRA/ MOUNTED EXTERN N 512 4096 4194304 20480 18780 0 18780 0 N REDO/
I cannot start ASM on my Oracle Database Appliance.
crsctl status resource -t says: ora.asm ONLINE ONLINE node1 Started ONLINE ONLINE node2 Started
however, if I try to access the ASMCMD it says: [grid@node1 ~]$ asmcmd Connected to an idle instance. ASMCMD> startup ORA-00304: requested INSTANCE_NUMBER is busy Connected to an idle instance.
I am using 10.2.0.1 on OEL5. I have installed CRS, Oracle home, ASM on both nodes and everything is fine.
When invoked dbca to create a database, it says:
*'DBCA could not startup the ASM instance configured on this node. To proceed with database creation using ASM you need the ASM instance to be up and running. Do you want to recreate the ASM instance on this node?'*
Is it a bug? because some blogs say this is a bug. And patch 8288940 will solve this. They also say this patch is to solve the incompatibility between 11g ASM incompatibility with 10g. But here I am using everything of 10.2.0.1.
I need to monitor the Database daily, so i need to check whether the size of the DB is increasing @ a slow rate or rather than that. I need to do it in RAC & NON-RAC.
Oracle DB version : 10.2.0.5.0 OS version HP-UX B.11.31 U ia64 DB size : 2TB
We have the above configuration for one of our oracle database. One of our DBA acts like he is only the person on this earth who is managing the process of fragmentation removal from the tablespaces in order to improve the performance and wastage of the space. He performs that task at weekends and takes one-off day extra. I am not sure how the fragmentation removal improves the performance and deallocates the space.
Is it compulsory to perform the rebuild process weekly in order to remove fragmented space from tablespaces? Do we have any other method to automatically re-organize objects occupying waste space?
Why the reuild of indexes using separate tablespace improves performance? is there any specifi reason for it?
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';
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?
I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB from dba_data_files a, dba_tables b, (select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c where a.tablespace_name = b.tablespace_name and b.owner = c.owner and b.table_name = c.table_name order by a.tablespace_name;
The output of the query is as shown in the attached file:
Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB, sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace" from dba_data_files a, dba_tables b, (select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c where a.tablespace_name = b.tablespace_name and b.owner = c.owner and b.table_name = c.table_name order by a.tablespace_name ; [code]...
Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.
tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.
In OEM10 I can click on the database size in the database page. When I do this I am redirected by the OEM to teh Database space usage report.
I have 3 questions about reports in OEM10g:
1. Can I create drill-down reports in OEM10g?I want to create a report that shows the space usage per host and drill down to the databases (targets) that are on that host.
2.Can I create a link to the Database space usage report? I would like to redirect to the report from the report in question 1 from a target. So that I can see the space usage of the database that was selected.
3. Can I create a copy of the Database Space Usage summary report that the OEM shows? I can't find it in the report tab of the OEM.
