SQL & PL/SQL :: Fetch Tablespace And Datafile Count With Used / Freespace?
Apr 30, 2013
I'm trying to fetch tablespace and its datafile count along with used space,freespace and total size of a tablespace. I've written a query like this, but the logic is wrong it is giving me incorrect rows. It is running in loops and shows me incorrect number of datafiles.
Quote: select DISTINCT b.tablespace_name "Tablespace Name",count(b.file_ID) "Num of Datafiles",
TO_CHAR( b.bytes/(1024*1024*1024),'990.00') "Total SIZE(GB)" ,
TO_CHAR((b.bytes - sum(nvl(a.bytes,0)))/(1024*1024*1024),'990.00') "Used Space(GB)" ,
TO_CHAR(sum(nvl(a.bytes,0))/(1024*1024*1024),'990.00') "Free Space(GB)"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name,b.file_name, b.file_id, b.bytes,online_status
order by b.tablespace_name;
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
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?.
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?
This facility has one last 10g database and a very problematic tablespace and last datafile associated with it. The tablespace was set up with INITIAL_ EXTENT of 131,072 (128K) instead of the more 'normal' 4,194,304 (4M) and NEXT_EXTENT of 262,144 (256K) instead of 4,194,304 (4M).
More worryingly, the datafile has INCREMENT_BY set to 1 (8K) instead of 1,280 (10M) or 2,048 (16M).Has anyone ever updated sys.ts$.dflinit and sys.ts$. dflincr to modify the INITIAL_EXTENT and NEXT_EXTENT, and sys.file$.inc to modify the INCREMENT_BY?
I am working on Oracle 11.2.0.3. The use of the DB is only for APEX.I installed APEX in a separate table space and datafile.When taking a full cold backup, is it enough to backup only two datafiles:
APEX.dbf : Where APEX was installed. APEX_2150521780606761.DBF:
The workspace datafile. Or, what is the most efficient way to take cold backup for Database that is dedicated for APEX ?
I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;
I've read(should have read it prior to action) [URL]tm
and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.
Is it safe to manually remove it by deleting it from the operating system?
The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
How to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C ---------- -------------------- -------------------- 1 2 3 2 5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C ---------- ---------- -------------------- -------------------- 1 1 2 3 2 2 3 5 4 [code]....
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
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.
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.
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?