We have quarterly and yearly processes that deletes and updates data for millions of rows crossing different tables. To make room on the file system I would either like to remove empty datafiles (my preference) or coalesce TS to compact the data than remove the empty datafile.
Having said that, any query that can show me if a datafile has any data in it or a query that can show me whether a TS is a good candidate to be coalesced maybe a query with something displaying a percentage as to free and used extents and a YES/NO column whether to be coalesced or not.
1) We have a 4TB database and using BIG FILE datafiles for all the datafiles(DATA,INDEX,UNDO and TEMP).
Few tables have grown extremely to consumed entries ASM disks. we have purged its data. so the the data file have the free space. However the data have another tables' LOB segment at the end of the datafile. so we could not shrik the data file.
we could do data pump export/import or moving the different tablespace. But we do not have ASM space. 11g Tablespace re-organization also require additional space(beleive equal amount of tablespace)
Is there any way to shink the data file?
2) let us assume my datafile is 190G and it has 80 free space with in the datafile and my ASM diskgroup size is 200G(4x50GB).
If I drop 1 ASM disk from my diskgroup, will it shrink the datafile becuase it has only free space ?
3) we are having TDE license...But not advanced compression...can we do segment movement with table compression?
We have large tables 60-70 GB having 120 million records. We have to perform index rebuild frequently which takes significant time to complete and effects database performance too. how we can use index Coalesce? what are its benefits, coalesce results in performance gain?
I use following command to determine which table can benefit from shrinking
select * from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) order by reclaimable_space desc
then i give following command to get reclaimable space
alter table t1 enable row movement ; alter table t1 shrink space cascade; alter table t1 disable row movement ;
in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.
Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc: ORA-01114: IO error writing block to file 1030 (block # 602122) ORA-27063: number of bytes read/written is incorrect IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: -1 Additional information: 180224
But this file_id i don't have in my database, i am making these queries:
SQL> select FILE_ID from dba_temp_files order by FILE_ID;
FILE_ID ---------- 1 2 3 4 5 6 7 8 9 [code]....
I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.
I am using 11GR2 and looking to find out a way , where in I able to extract the data files name in such a manner , where it lists data file in order of mount points. say data1 first and then so on.. Snippet from my data files
like /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data23/sans01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf
I have a tablespace which has around 32gb space consumed. But if i check the used space then its only 16GB. When i tried to resize the datafile it throws the error
ORA-03297: file contains used data beyond requested RESIZE valueAs per my understanding there are not continous blocks which are there in datafile due to fragmentation may be and there by not able to resize it. If i export the tablespace using datapump and reimport this will release the space.
But i want to know if there are any alternative ways to do the same.
In my Production DB. 5 Datafiles created in same tablespace. Datafile size is of 25GB. Data stored in all Datafile. Data is just 5GB in all datafile. I want to move data from 5 datafiles to single or couple of datafiles.
I have a small problem, i will try to explain it, My tablespaces are Automatically extend datafile when full (AUTOEXTEND). It had checked "autoextend ON" checkbox.
What I need to know is, when happened that autoextend accion(day/hour), is it possible?.
Suppose our database have 100 datafiles and right now rman has completed backup of 64 datafile(from file no 1 to file no 64) and in process of backup of 65 number datafile. In mean time I executed some query and it has changed the file no 55. so now my question whether rman will go and bacup this datafile(datafile no 55) again or it will leave this file as it is?
I need to change data file path, and i got some document also but one thing confusing me, we need to offline the Tablespace, so my concern is all the table space like system, sys, user, temp etc. need to offline and then alter the database?
I can not drop datafile in a tablespace, how can i do?
SQL> Alter Database Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf' Offline 2 3 /
Database altered.
SQL> Alter Tablespace tps_hxl Drop Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'; 2 Alter Tablespace tps_hxl * ERROR at line 1: ORA-03264: cannot drop offline datafile of locally managed tablespace
I have Tow Virtual Servers (Connect As Cluster) in the Same Computer The Files In the Drive C Different (of the Tow Servers) And Others Like (e,f,q) The Same Files
And There is Database of both Servers That Have Same Name Let say orcl and both database Have same Schema let Say sch How I can Share The Data when i am insert to table. in the schema sch in the server A i can see Same Data That I have Inserted In the Schema sch in the server B.
How can i check for datafile corruption or a datafile error,its some tool like Linux (fsck) command in Oracle that can halp to me to chack a datafile?.
Is any way to isolate the disk region that is corrupted?
If a disk error exist an an ASM disk group how can i isolate the error from that disk group? It's an alternative different to the VALIDATE DATAFILE command in RMAN?I'm using Oracle 11GR2 on a Linux Box.
I have oracle 10g running on Solaris with file system and some one created database files with same name but in different directories for example data01.dbf in two different directories, say /u01/oradata/data01.dbf and /u02/oradata/data01.dbf. Now, I want to find out the duplicate datafiles (data01.dbf in this case) sitting in different directories, is there anyway to find this out?
We have cloned the db and after opening the db with resetlog option we came to know that one of the user db file is not part of the db and is showing the below error while checking the status and attempting to online it. When checking the size and availability of the dbfile it shows everything without errors.
SQL> alter database datafile 50 online; alter database datafile 50 online * ERROR at line 1: ORA-01122: database file 50 failed verification check ORA-01110: data file 50: '+DATA/orcl/orcldata.291.730828357.dbf' ORA-01251: Unknown File Header Version read for file number 50
develop a Oracle stored function or procedure to confirm a availability of datafile on a specific local directory to be read by Oracle External table. The fine looks like filenameddmmyyyy.csv.
I have create a Oracle directory named data_dir that is mapped with physical directory c: mep
Once it is confirmed that datafile is available the ETL process is started.
How datafile is created with 640 file permission by oracle software.(I know for security reasons it is created with 640 permission)
example: -rw-r----- 1 orasd dba 104865792 Mar 15 01:17 users01.dbf
I want to change the datafile permission as below. how to change the default value of the datafiles permission from (640) to (644) internally while creating the datafile from oracle side.
example: -rw-r--r-- 1 orasd dba 104865792 Mar 15 01:17 users01.dbf
Today i am facing an error when going to rezise the datafile its fixed size is 19000M abut after truncating all tables it is 112M, but when i am going to resize its datafile to 500M its get me an error ora-03297 file contain used data beyond requested size values.
I have done the same before a week without any error.But this time i got the error
Basic situation is this: Oracle 10g database, good RMAN backup from a few days ago. We lost one datafile due to storage corruption. I restored the datafile from RMAN, no problem, and started to roll forward with our archive logs. Problem: one log is corrupted, can't be read, no other copies of it. We are stuck, cannot roll forward any further. The database is up (all activity suspended for now) -- only this one file is offline. Everything else is fine.
My question: Can i do something similar to "open resetlogs" with just the one datafile? Most of what was in the file was indexes, which can be easily dropped & recreated. But a few extents were data which I want to retain. It's old data and the archive logs subsequent to the one on which we are stuck should be pretty much irrelevant. So if i can 'force' the datafile to open up, i believe we should be OK with the little bit of data that's in there, even if we don't apply the remaining archive logs. If it's not 100% consistent we can live with that.
Datafile name is +DISK_GROUP_1/oratst1/datafile/system.1122.764387443
displayed for the SYSTEM tablespace in dba_data_files.Infact, datafile name starts with +DISK_GROUP_1 for tablesspaces. Where is the reference to +DISK_GROUP_1.
What file name should I use to resize or to add new datafile to a tablespace?
Erroneously created datafile, re: "/path/../large_rbs_03.dbf" was created under the SYSTEM tablespace which is supposedly be in the LARGE_RBS tablespace.
How do I make the said datafile be under LARGE_RBS?
