Tablespace &Data files Queries

select name from v$datafile;

select name from v$logfile;

select name from v$controlfile;

select file_name,tablespace_name from dba_data_files where file_name like '%media%';-Find  the files contains  media---

select file_name,status from dba_data_files where tablespace_name = 'APPS_TS_MEDIA'; - Shows the data files  under particular TS

Rename the data file:

1.alter tablespace APPS_TS_MEDIA offline;

2.cp from Os

3.alter tablespace APPS_TS_MEDIA rename datafile '/testdb/TEST/testdata/logdata/media_6.dbf' to '/testdb/TEST/testdata/appsdata/media_6.dbf'

                                 or

Shut DB,Start Mount,ALTER DATABASE RENAME file '/testdb/TEST/testdata/logdata/media_6.dbf'  to '/testdb/TEST/testdata/appsdata/media_6.dbf';

4.alter tablespace APPS_TS_MEDIA offline;

To see the all tablespaces total MB, used MB, free MB, used and free order min

set linesize 170

SELECT TabSpaceName,

SUM(TotBytes) "TOTAL MB",

SUM(BytesUsed) "USED MB",

SUM(BytesFree) "FREE MB",

AVG(PCUSED) "% USED",

AVG(PCFREE) "% FREE"

FROM (

SELECT SUBSTR (df.file_id, 1, 3) "ID#",

df.tablespace_name TabSpaceName,

df.BYTES/1024/1024 TotBytes,

round (NVL(df.BYTES/1024/1024 - SUM (fs.BYTES/1024/1024),df.BYTES/1024/1024)) BytesUsed,

round (NVL(SUM (fs.BYTES/1024/1024),0)) BytesFree,

round (NVL((100 * ((SUM (fs.BYTES)) / df.BYTES)),0)) PCFREE,

round (NVL((100 * ((df.BYTES - SUM (fs.BYTES)) / df.BYTES)),100)) PCUSED

FROM SYS.dba_data_files df,

SYS.dba_free_space fs

WHERE df.file_id = fs.file_id (+)

GROUP BY df.tablespace_name, df.file_id,

df.tablespace_name,

df.BYTES,

df.blocks)

GROUP BY TabSpaceName

ORDER BY 6 ;

To Find Database  Instance name,Startup Time,Host Name,Status,Archivelog Mode

COLUMN "Instance" FORMAT a10

COLUMN "Startup Time" FORMAT a16

COLUMN "Status" FORMAT a08

COLUMN "Archivelog Mode" FORMAT a18

COLUMN "Logins" FORMAT a12

COLUMN "Archiving" FORMAT a10

COL "HOST_NAME" format a30

set linesize 132

select i.instance_name "Instance" ,i.host_name,

TO_CHAR(i.startup_time, 'MM/DD/YY HH24:MI') "Startup Time",

i.status "Status",

d.log_mode "Archivelog Mode",

i.logins "Logins",

i.archiver "Archiving"

from v$instance i, v$database