Server Administration :: Find Tablespace Usage Including Datafiles

May 25, 2010

I am looking for a script to find the tbs usage including datafiles. Tablespaces Datafile Size Used Free Used% Free% Max MB

Need all the data in MB and all the datafile space in every tablespace.I have searched internet,but couldn't find this type of format.

View 9 Replies


ADVERTISEMENT

Backup & Recovery :: Dropping Tablespace Including Contents And Datafiles

Jun 17, 2013

1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.

2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.

List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF

3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"

4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.

SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
[code]....

how to recover those tablespace's datafile?What happened to earlier tablesapce backup?

View 9 Replies View Related

Server Administration :: Removing A Tablespace And Its Associated Datafiles?

Jul 12, 2010

Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:

"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"

The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.

Do I have to do it online or offline?

View 1 Replies View Related

Server Administration :: Adding Datafiles To Tablespace

Dec 20, 2011

Can we add a datafile mapped to a shared network drive in windows server.

View 1 Replies View Related

Server Administration :: To Reduce Number Of Datafiles In Tablespace

Feb 25, 2012

We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .

View 1 Replies View Related

SQL Query To Find CPU Usage And Memory Usage

Jun 28, 2013

Is their any query to find cpu usage & memory usage of all the queries currently running on DB? 

View 5 Replies View Related

Server Administration :: MR Lock / Show All Datafiles

Nov 25, 2010

Just noticed MR(media recovery lock ) in our database,further check showd all datafiles had this lock since our database was shutdown abortly but was startup gracefully.

View 2 Replies View Related

Server Administration :: 10g Change Disk With Datafiles

Nov 17, 2011

What is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.

I.
1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.

II.
1.Stop application.
2.CONNECT AS SYSDBA
3.SHUTDOWN NORMAL or (IMMEDIATE)?
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.

View 1 Replies View Related

Server Administration :: Why 3 Mount Points For Datafiles

Feb 3, 2013

i encoutered lately on such statement:

Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....

why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?

View 4 Replies View Related

Server Administration :: Estimating Size Of Database / Datafiles

Jul 3, 2010

I have checked the space of my tablespaces/datafiles in my database. I have 8 GB space left in my database server. I cant add more hard-disk as there is no slot left. We r planning to buy a new server with latest config.

My question is, how can we know upto what size our database can increase and when a datafile need to be added in advance. Sometimes even though datafiles have space left,it shows errors abt extents cannot be extended. We have coalesce the tablespaces and added a new datafile.

View 5 Replies View Related

Server Administration :: RMAN Restore Failure All Datafiles

Jul 1, 2013

I am facing an issue while rebuilding a DR (Physical Standby) site using RMAN.Rman is failing to restore all the datafiles to the stanby site.

There are 403 datafiles that should be restored among which after restoring 50-60 datafiles the channel is abruptly getting terminated.Please note this exercise is not new I have done this many times (once a year after a DR testing) and the same script is being used.The rcv file is as follows

/u03/oracle/admin/mpr/scripts/dataguard/configure_dr.rcv
connect auxiliary sys/XXXX@YYY
run {
duplicate target database for standby nofilenamecheck dorecover;
}
exit
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
configure_dr.rcv: END
[code]....

The trace file says "krso_proc_add: Detected dead process 13828326; subsuming V$MANAGED_STANDBY slot
INFO: krsr_rfs_atc: RFS processes are temporarily disallowed"

I have also started RMAN debug tracing and the trace is also scanned as it is huge.I have seen the following in the trace error 3015 signalled during compilation..I do not find any specific oracle error to check on .

The only change that happened is that the Primary production server has been moved to a new datacentre and after its move all the connections seems to be working fine.

View 3 Replies View Related

Server Administration :: Check Tables Located In Which Datafiles?

Aug 6, 2010

would want to check which tables located in which datafiles.

Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'

Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.

View 1 Replies View Related

Server Administration :: Big Datafile Vs Multiple Small Datafiles

Jun 17, 2010

what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.

View 3 Replies View Related

Server Administration :: Recover Database With Some Datafiles And Archivelogs?

Dec 24, 2010

We have lost some datafiles and some archivelogs. We have backup of whole database taken on Dec 20 and archivelogs until Dec 23.So we dont have 1 day worth of archivelogs which can lead to incomplete recovery.

Now question is do we need to restore all datafiles or only lost datafiles for incomplete recovery since we dont have all archivelogs ? lost datafiles belong to different tablespaces.

Datafiles which are still intact are with current SCN and we restore lost datafiles only and recover unitl our last available archivelog it will have different SCN. Will we be able to open database or we need to resotre whole database for this scenario?

View 8 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

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

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

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.

View 7 Replies View Related

Server Administration :: CPU Usage By Oracle 10g Database

Aug 16, 2012

I have three oracle 10g databases (db1, db2, db3)on a linux box.

A command at the OS level to find total cpu used by each of the above databases.

View 12 Replies View Related

Delete One Datafile From Tablespace Which Contain 2 Datafiles?

Oct 29, 2008

i want to delete one datafile from tablespace witch contain 2 datafiles

i did

ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;

and delete the file physiquly but i have already my datafile witch i have delete in my tablespace

View 14 Replies View Related

Dynamic Alter Tablespace Statement To Add Datafiles

Jan 21, 2013

I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.

View 6 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

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.

View 6 Replies View Related

How To Find CPU Usage By Each User Session

Sep 7, 2011

I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?

Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.

View 5 Replies View Related

Server Administration :: Create Tablespace For Administration

Nov 29, 2010

i'm a student currently learning database administration security.

I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

View 12 Replies View Related

Server Administration :: System TableSpace Is So Big?

Sep 26, 2011

Today I see my system tablespace is 98%, 480 MB used. Why the system tablespace is so big?

View 5 Replies View Related

Server Administration :: Tablespace On Certain Workstations

Mar 5, 2012

I am NOT a db admin but I do 'maintain' my application that runs with oracle 8i. I am now getting the error on certain workstations:

ORA-03232: unable to allocate an extent of 16 blocks from tablespace 3

I have access to DBA Studio and all the tables in the STORAGE tab show no more than 65% usage

View 7 Replies View Related

Server Administration :: Shrink Tablespace

Jun 7, 2010

I have a tablespace with 5GB size, data in the tablespace is 4.8GB. if i want to shrink the tablespace then much size could be shrinked.

View 4 Replies View Related

Server Administration :: Tablespace Creation

Jun 24, 2012

Problem Description: There are five range partitioned tables which are partitioned by date,designed by previous DBA.Four tables are in one tablespace and one more table is in another tablespace.For every three months there are two tablespaces are created autamatically in new names for future dates.There is no scheduled job for this.How they are created?.This is our question.

View 3 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related

Server Administration :: System Tablespace Had Been Used More?

May 4, 2012

In my database ,the size of the system tablespace is 1024M.now i found its free space is 477.875M.

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 M FROM DBA_FREE_SPACE where tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME ;
TABLESPACE_NAME M
--------------- -------
SYSTEM 477.875

In system tablespace ,the max size segment is IDL_UB1$,it is 167M

SQL> SELECT SEGMENT_NAME, SX || 'M' "Size" FROM (SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 SX FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME) WHERE SX > 100 ORDER BY SX DESC;
SEGMENT_NAME Size
------------- -----
IDL_UB1$ 167M

QUESTION: what's the table IDL_UB1$ used for ?

View 4 Replies View Related

Server Administration :: Alter Tablespace

Jan 4, 2011

My tablespace contains two datafiles dfile1.dbf and dfile2.dbf on D drive of my filesystem. Now i copied these dbf files on pasted it on a location in E drive. Now i want my tablespace to use dbf files pasted in new location.

Will i have to Alter Tablespace Add Datafile with Reuse clause

View 2 Replies View Related

Server Administration :: RBS Tablespace Is Not Releasing Space

Aug 27, 2012

In 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

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved