SQL script to check available space in your recovery area

SQL script to check available space in your recovery area (db_recovery_file_dest_size)

col name for a32
col size_m for 999,999,999
col reclaimable_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999

SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, ceil( space_reclaimable / 1024 / 1024) RECLAIMABLE_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( ( space_used - space_reclaimable ) / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/

or


col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/

SQL Net* break message

SQL Net* break message

SELECT S.LOGON_TIME, --s.LAST_CALL_ET "idel in sec", floor(s.LAST_CALL_ET/86400) || 'd ' || to_char(to_date(mod  (s.LAST_CALL_ET,86400) ,'sssss'),'hh24"h" mi"m" ss"s"') "Idle Time", S.username,S.sid,S.serial#,S.status,S.program,S.module,  s.machine, s.event, 'alter system kill session ' ||''''||s.sid||','||s.serial#||''''||'  IMMEDIATE;' Kill_Stmt --,'alter system kill session ' ||''''||s.sid||','||s.serial#||',@'||inst_id||''''||'  IMMEDIATE;' Kill_Stmt FROM gV$SESSION SWHERE 1=1 --  and S.logon_time  <sysdate  --   and s.type='USER' and s.status<> 'ACTIVE' --and s.LAST_CALL_ET>86400/48 --and s.event='SQL*Net break/reset to client' --and( s.machine = 'anb-srl-ogcoms.anb,net' or  s.terminal ='anb-srl-ogcoms.anb.net') ORDER BY 2 desc;

ASM Diskgroup usage

ASM Diskgroup usage

select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number and dg.name like '%MQM_DG%' group by dg.name order by dg_name;


select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number group by dg.name order by dg_name;

RMAN BACKUP STATUS REPORT

RMAN BACKUP STATUS REPORT

set linesize 1000

set pagesize 1000

select I.instance_name,R.SESSION_KEY, R.INPUT_TYPE, R.STATUS, R.start_time,R.end_time,"Minutes" 

from (

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'dd/mm/yyyy hh24:mi') start_time,

to_char(END_TIME,'dd/mm/yyyy hh24:mi') end_time,

round((end_time-start_time)*1440,2) "Minutes"

from V$RMAN_BACKUP_JOB_DETAILS

where to_char(start_time,'yyyymmdd') between '20230101' and '20231231' --and input_type='DB INCR'

order by session_key desc

) R, v$instance I 

where rownum < 10;

Invalid Objects In Oracle Database ?

Invalid Objects In Oracle Database ?

select created,owner,object_name,object_type from dba_objects where status='INVALID' order by 1;

select owner,count(*) from dba_objects where status='INVALID' group by owner ORDER BY 2 DESC;

select owner,count(*) from dba_objects where status='INVALID' group by owner;

--Inv. creation date

select created,owner,object_name,object_type,status from dba_objects where created >=sysdate -1 and owner not in ('SYS','PUBLIC') order by 1 desc;

How to check database startup time ?

How to check database startup time ?

set pages 9999 lines 300

col open_mode for a10

col host_name for a20

select name db_name,instance_name,host_name,database_role,open_mode,version db_version,logins,

to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "db up time" from v$database,gv$instance;

How to truncate audit tablespace ?

How to truncate audit tablespace ?

select sum(bytes)/(1024*1024) "Table size(MB)" from dba_segments where segment_name='AUD$' and owner='SYS';

select count(*) from sys.aud$;

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB Size" from dba_segments where segment_name='AUD$';

TRUNCATE table sys.AUD$;

select count(*) from sys.aud$;

How To Shrink Tablespaces

How To Shrink Tablespaces

select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) resize_GB, ceil( blocks*&&blksize/1024/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) reclaimable_GB from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+)
/

---With alter command individual tablespace.

select file_name,'alter database datafile'||' '''||file_name||''''||' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 )||''||'G'||';', ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) resize_GB, ceil( blocks*&&blksize/1024/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) reclaimable_GB from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and a.tablespace_name='SYSTEM' and ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 )!=0 order by 5 desc /

---with alter command.

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc
/

---with alter command in GB

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5GB minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024/1024),999999)
||'GB from '||to_char(ceil(bytes/1024/1024/1024),999999)||'GB */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024/1024)||'GB;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024/1024),999999)
||'GB from '||to_char(ceil(bytes/1024/1024/1024),999999)
||'GB after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL from hwmdf where bytes-hwm_bytes>1024*1024*1024 -- resize only if at least 1GB can be reclaimed order by bytes-hwm_bytes desc
/

Day 2 day queries

Day 2 day queries

To Find DB/Instance Name in RAC 

=========================

sql> show parameter cluster;

col host_name for a20

select inst_id,instance_name,host_name,instance_role from gv$instance;

select instance_name from v$instance;

To check from the operating system

 

# lsnodes

To get the NLS PARAMETER details 

================================

set linesize 150

set pagesize 1000

col name for a50

col parameter for a30

col value for a30

col member for a50

col DATAFILE_NAME for a70

col TEMPFILE_NAME for a70

col CONTROLFILE_NAME for a70

col LOGFILE_MEMBER for a70

select * from NLS_DATABASE_PARAMETERS;

To check the Instance Uptime 

================================

select instance_name||', up since '|| to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') start_time from gv$instance;

To Identify Space occupied and original space utilized of a database 

=====================================================================

set numf 999999.99

select sum(bytes)/(1024*1024*1024) "Total Allocated GB",Total_Consumed_GB from dba_data_files,

(select sum(bytes)/(1024*1024*1024) Total_Consumed_GB from dba_segments) group by Total_Consumed_GB;

To get the DATABASE SCHEMA SIZE from the database 

===================================================

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

To get the LOGFILES INFORMATION from the database 

==================================================

col member for a65

select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 "BYTES IN MB",ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#

To get the CONTROLFILES INFORMATION from the database 

=======================================================

col name for a90

select * from v$controlfile

To get the TEMPFILES SIZE from the database 

=============================================

select tablespace_name,file_name,bytes/1024/1024 "SIZE_IN_MB" from dba_temp_files order by file_name asc;

To get the TABLESPACE SIZE from the database 

==============================================

select tablespace_name,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_data_files group by tablespace_name;

To get the datafile sizes from the database

================================================

col file_name for a70

col tablespace_name for a30

clear breaks

clear computes

compute sum of SIZE_IN_MB on report

break on report

select tablespace_name,file_name,AUTOEXTENSIBLE,INCREMENT_BY,MAXBYTES/1024/1024 "MAX in MB",bytes/1024/1024 "SIZE_IN_MB" from dba_data_files order by tablespace_name;

To get information about the database 

======================================

SET LINESIZE 150

SET PAGESIZE 50000

cOL HOST_NAME FOR A25

col LOGINS FOR A20

col STATUS for A15

col "STARTUP_TIME" FOR A30

col INSTANCE_NAME for a20

col VERSION for a20

select INSTANCE_NAME,HOST_NAME,VERSION,LOGINS,STATUS,to_char(STARTUP_TIME,'DD-MON-YYYY DAY HH24:MI:SS')

"STARTUP_TIME" FROM v$instance;

col name for a30

col "CREATED" for a25

col LOG_MODE for a15

col OPEN_MODE for a15

col DATABASE_ROLE for a15

select NAME,to_char(CREATED,'DD-MON-YYYY HH24:MI:SS') "CREATED",LOG_MODE,OPEN_MODE,DATABASE_ROLE from v$database;

############33Procedure for generating the DDL statements of the database objects. 

=========================================================================

There are two parameters used in the specified object,

  

1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,

2. Directory Name  ==> Specify the directory name where the output to be stored.

Usage -  DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:

----------------------

 CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE" 

   (    "COMNAME" VARCHAR2(200 BYTE), 

    "COMDDDL" LONG

   );

Create or Replace

PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)

AS

V_DDL LONG;

V_ERRMSG VARCHAR2(500);

vInHandle  utl_file.file_type;

CURSOR C1 IS 

SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;

BEGIN

FOR I IN C1 LOOP

select 

dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME)  INTO V_DDL

from dual;

  vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');

  IF utl_file.is_open(vInHandle) THEN

   utl_file.put_line(vInHandle, V_DDL, FALSE);

    utl_file.fflush(vInHandle);

    utl_file.fclose_all;

 END IF;

END LOOP;

EXCEPTION WHEN OTHERS THEN

V_ERRMSG:=SQLERRM;

INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);

END;

-- +==================================================================+

-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type

-- +==================================================================+

SET LINESIZE 155

SET PAGESIZE 9999

SET VERIFY   OFF

BREAK ON segment_type SKIP 1

COMPUTE SUM OF bytes ON segment_type

COLUMN segment_type        FORMAT A20                HEADING 'Segment Type'

COLUMN owner               FORMAT A15                HEADING 'Owner'

COLUMN segment_name        FORMAT A30                HEADING 'Segment Name'

COLUMN partition_name      FORMAT A30                HEADING 'Partition Name'

COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace Name'

COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'

COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

SELECT

    a.segment_type      segment_type

  , a.owner             owner

  , a.segment_name      segment_name

  , a.partition_name    partition_name

  , a.tablespace_name   tablespace_name

  , a.bytes             bytes

  , a.extents           extents

FROM

    (select

         b.segment_type

       , b.owner

       , b.segment_name

       , b.partition_name

       , b.tablespace_name

       , b.bytes

       , b.extents

     from

         dba_segments b

     order by

         b.bytes desc

    ) a

WHERE

    rownum < 101

ORDER BY

    segment_type, bytes desc, owner, segment_name

/

To check for index fragmentation 

 -- +------------------------------------------------------------------------------+

-- | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |

-- |            of the table data changes, then you should consider rebuilding the index  |

-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE; 

COL name         HEADING 'Index Name'          FORMAT a30 

COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999 

COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999 

COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999 

SELECT

    name

  , del_lf_rows

  , lf_rows - del_lf_rows lf_rows_used

  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 

FROM   index_stats

/

prompt 

prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%

prompt 

undefine index_name 

Report free space fragmentation 

-- +----------------------------------------------------------------------------+

-- | PURPOSE  : Report free space fragmentation.                                             |

-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                  |

-- +----------------------------------------------------------------------------+

connect / as sysdba

CREATE OR REPLACE VIEW free_space (

    tablespace

  , pieces

  , free_bytes

  , free_blocks

  , largest_bytes

  , largest_blks

  , fsfi

  , data_file

  , file_id

  , total_blocks

)

AS

SELECT

    a.tablespace_name

  , COUNT(*)

  , SUM(a.bytes)

  , SUM(a.blocks)

  , MAX(a.bytes)

  , MAX(a.blocks)

  , SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))

  , UPPER(b.file_name)

  , MAX(a.file_id)

  , MAX(b.blocks)

FROM

    sys.dba_free_space  a

  , sys.dba_data_files  b

WHERE

    a.file_id = b.file_id

GROUP BY

    a.tablespace_name,  b.file_name

/

CLEAR COLUMNS

SET LINESIZE  120

SET PAGESIZE  9999

SET FEEDBACK  off

SET VERIFY    off

BREAK ON tablespace SKIP 2 ON REPORT

COMPUTE SUM OF  total_blocks  ON tablespace

COMPUTE SUM OF  free_blocks   ON tablespace

COMPUTE SUM OF  free_blocks   ON report

COMPUTE SUM OF  total_blocks  ON report

COLUMN tablespace     HEADING "Tablespace"    FORMAT a15

COLUMN file_id        HEADING File#           FORMAT 99999

COLUMN pieces         HEADING Frag            FORMAT 9999

COLUMN free_bytes     HEADING 'Free Byte'

COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999

COLUMN largest_bytes  HEADING 'Biggest Bytes'

COLUMN largest_blks   HEADING 'Biggest Blks'  FORMAT 999,999,999

COLUMN data_file      HEADING 'File Name'     FORMAT a45

COLUMN total_blocks   HEADING 'Total Blocks'  FORMAT 999,999,999

SELECT

    tablespace

  , data_file

  , pieces

  , free_blocks

  , largest_blks

  , file_id

  , total_blocks

FROM

    free_space

/

DROP VIEW free_space

/

List all currently connected user sessions ordered by current PGA size 

-- +-------------------------------------------------------------------------------------+

-- | PURPOSE  : List all currently connected user sessions ordered by current PGA size  |

-- +-------------------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid                     FORMAT 99999          HEADING 'SID'

COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'

COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right

COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC

COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'

COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'

COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'

COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

prompt 

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by Current PGA Size          |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,8)    session_machine

  , sstat1.value         session_pga_memory

  , sstat2.value         session_pga_memory_max

  , sstat3.value         session_uga_memory

  , sstat4.value         session_uga_memory_max

FROM 

    v$process  p

  , v$session  s

  , v$sesstat  sstat1

  , v$sesstat  sstat2

  , v$sesstat  sstat3

  , v$sesstat  sstat4

  , v$statname statname1

  , v$statname statname2

  , v$statname statname3

  , v$statname statname4

WHERE

      p.addr (+)            = s.paddr

  AND s.sid                 = sstat1.sid

  AND s.sid                 = sstat2.sid

  AND s.sid                 = sstat3.sid

  AND s.sid                 = sstat4.sid

  AND statname1.statistic#  = sstat1.statistic#

  AND statname2.statistic#  = sstat2.statistic#

  AND statname3.statistic#  = sstat3.statistic#

  AND statname4.statistic#  = sstat4.statistic#

  AND statname1.name        = 'session pga memory'

  AND statname2.name        = 'session pga memory max'

  AND statname3.name        = 'session uga memory'

  AND statname4.name        = 'session uga memory max'

ORDER BY session_pga_memory DESC

/

List all currently connected user sessions ordered by Logical I/O 

-- +--------------------------------------------------------------------------------+

-- | PURPOSE  : List all currently connected user sessions ordered by Logical - I/O |

-- +--------------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid               FORMAT 99999            HEADING 'SID'

COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'

COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right

COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC

COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN logical_io        FORMAT 999,999,999,999  HEADING 'Logical I/O'

COLUMN physical_reads    FORMAT 999,999,999,999  HEADING 'Physical Reads'

COLUMN physical_writes   FORMAT 999,999,999,999  HEADING 'Physical Writes'

prompt 

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by Logical I/O               |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,8)    session_machine

  , sstat1.value

    + sstat2.value         logical_io

  , sstat3.value         physical_reads

  , sstat4.value         physical_writes

FROM 

    v$process  p

  , v$session  s

  , v$sesstat  sstat1

  , v$sesstat  sstat2

  , v$sesstat  sstat3

  , v$sesstat  sstat4

  , v$statname statname1

  , v$statname statname2

  , v$statname statname3

  , v$statname statname4

WHERE

      p.addr (+)            = s.paddr

  AND s.sid                 = sstat1.sid

  AND s.sid                 = sstat2.sid

  AND s.sid                 = sstat3.sid

  AND s.sid                 = sstat4.sid

  AND statname1.statistic#  = sstat1.statistic#

  AND statname2.statistic#  = sstat2.statistic#

  AND statname3.statistic#  = sstat3.statistic#

  AND statname4.statistic#  = sstat4.statistic#

  AND statname1.name        = 'db block gets'

  AND statname2.name        = 'consistent gets'

  AND statname3.name        = 'physical reads'

  AND statname4.name        = 'physical writes'

ORDER BY logical_io DESC

/

List all currently connected user sessions ordered by CPU time. 

-- +----------------------------------------------------------------------------+

-- | DATABASE : Oracle |

-- | PURPOSE : List all currently connected user sessions ordered by CPU time. |

-- +----------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING 'SID'

COLUMN serial_id FORMAT 999999 HEADING 'Serial#'

COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right

COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right

COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right

COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right

COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC

COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC

COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'

prompt

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by CPU |

prompt +----------------------------------------------------+

SELECT

s.sid sid

, s.serial# serial_id

, lpad(s.status,9) session_status

, lpad(s.username,12) oracle_username

, lpad(s.osuser,9) os_username

, lpad(p.spid,7) os_pid

, s.program session_program

, lpad(s.machine,14) session_machine

, sstat.value cpu_value

FROM

v$process p

, v$session s

, v$sesstat sstat

, v$statname statname

WHERE

p.addr (+) = s.paddr

AND s.sid = sstat.sid

AND statname.statistic# = sstat.statistic#

AND statname.name = 'CPU used by this session'

ORDER BY cpu_value DESC

/

Audit Unsuccessful Logins

Audit Unsuccessful Logins

select username,os_username, userhost, client_id, trunc(timestamp),count(*) failed_logins from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password timestamp > sysdate -7 group by username,os_username,userhost, client_id,trunc(timestamp);

To get more details on the record found for "ALTER USER" command issued:


SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL /tmp/AUDIT_INFO.html
select * from dba_audit_trail where action_name='ALTER USER' and timestamp> sysdate-10;
select * from unified_audit_trail where EVENT_TIMESTAMP > sysdate-10;


SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
Alter user commad from dba_audit_trail:

SQL> select username,TIMESTAMP,ACTION_NAME,RETURNCODE,SQL_TEXT from dba_audit_trail where ACTION_NAME like '%ALTER USER%';

How to check the password_verify_function:

set lines 170
col owner format a30
col name format a45
col text format a50
select * from dba_source where owner = 'SYS' and name in(select limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION' and limit <> 'NULL') order by name, line;


How to check the dba profile from sqlplus:

set lines 170
col profile format a20
col limit format a30
select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from dba_profiles where PROFILE='<Profile_name>';

How to check the user status and details:
SQL> select USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE from dba_users where username ='User_name';


How to check the last_login details:

SQL> SELECT USERNAME, LAST_LOGIN FROM DBA_USERS WHERE USERNAME='User_name';

Find recently modified tables in Oracle database

Find recently modified tables in Oracle database

select a.OWNER, a.SEGMENT_NAME "TABLE_NAME", B.OBJECT_TYPE,b.CREATED, b.LAST_DDL_TIME "MODIFIED" , a.BYTES/1024/1024 "SIZE_MB"

from dba_segments a, dba_objects b

where a.OWNER=B.OWNER

AND A.SEGMENT_NAME=B.OBJECT_NAME

AND A.SEGMENT_TYPE=B.OBJECT_TYPE

and a.owner not in (select name from system.logstdby$skip_support where action = 0);

select tab.owner as table_schema,

       tab.table_name,

       obj.last_ddl_time as last_modify

from all_tables tab

join all_objects obj on tab.owner = obj.owner

     and tab.table_name = obj.object_name

where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',

     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',

     'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',

     'WKSYS','WKPROXY','WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP',

     'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',

     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')

      and obj.last_ddl_time > (current_date - INTERVAL '60' DAY)

order by last_modify desc;

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

   owner, 

   table_name, 

   TRUNC(sum(bytes)/1024/1024) Meg,

   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent

FROM

(SELECT segment_name table_name, owner, bytes

 FROM dba_segments

 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

 UNION ALL

 SELECT i.table_name, i.owner, s.bytes

 FROM dba_indexes i, dba_segments s

 WHERE s.segment_name = i.index_name

 AND   s.owner = i.owner

 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

 UNION ALL

 SELECT l.table_name, l.owner, s.bytes

 FROM dba_lobs l, dba_segments s

 WHERE s.segment_name = l.segment_name

 AND   s.owner = l.owner

 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')

 UNION ALL

 SELECT l.table_name, l.owner, s.bytes

 FROM dba_lobs l, dba_segments s

 WHERE s.segment_name = l.index_name

 AND   s.owner = l.owner

 AND   s.segment_type = 'LOBINDEX')

WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

-- Tables + Size MB

select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 

from all_tables 

where owner not like 'SYS%'  -- Exclude system tables.

and num_rows > 0  -- Ignore empty Tables.

order by MB desc -- Biggest first.

;

--Tables + Rows

select owner, table_name, num_rows

 from all_tables 

where owner not like 'SYS%'  -- Exclude system tables.

and num_rows > 0  -- Ignore empty Tables.

order by num_rows desc -- Biggest first.

;

SELECT * FROM (

  SELECT

    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

    tablespace_name, extents, initial_extent,

    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

  FROM (

    -- Tables

    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

          segment_name AS table_name, bytes,

          tablespace_name, extents, initial_extent

    FROM   dba_segments

    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

    UNION ALL

    -- Indexes

    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,

          i.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_indexes i, dba_segments s

    WHERE  s.segment_name = i.index_name

    AND    s.owner = i.owner

    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

    -- LOB Segments

    UNION ALL

    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,

          l.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_lobs l, dba_segments s

    WHERE  s.segment_name = l.segment_name

    AND    s.owner = l.owner

    AND    s.segment_type = 'LOBSEGMENT'

    -- LOB Indexes

    UNION ALL

    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,

          l.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_lobs l, dba_segments s

    WHERE  s.segment_name = l.index_name

    AND    s.owner = l.owner

    AND    s.segment_type = 'LOBINDEX'

  )

  WHERE owner = UPPER('&owner')

)

WHERE total_table_meg > 10

ORDER BY total_table_meg DESC, meg DESC

/

SELECT

   owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type in  ('TABLE','TABLE PARTITION')

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND   s.owner = i.owner

AND   s.segment_type in ('INDEX','INDEX PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND   s.owner = l.owner

AND   s.segment_type IN ('LOBSEGMENT','LOB PARTITION')

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND   s.owner = l.owner

AND   s.segment_type = 'LOBINDEX')

---WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */

ORDER BY SUM(bytes) desc;

SELECT * FROM (

  SELECT

    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

    tablespace_name, extents, initial_extent,

    ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

  FROM (

    -- Tables

    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

          segment_name AS table_name, bytes,

          tablespace_name, extents, initial_extent

    FROM   dba_segments

    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

    UNION ALL

    -- Indexes

    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,

          i.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_indexes i, dba_segments s

    WHERE  s.segment_name = i.index_name

    AND    s.owner = i.owner

    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')

    -- LOB Segments

    UNION ALL

    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,

          l.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_lobs l, dba_segments s

    WHERE  s.segment_name = l.segment_name

    AND    s.owner = l.owner

    AND    s.segment_type = 'LOBSEGMENT'

    -- LOB Indexes

    UNION ALL

    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,

          l.table_name, s.bytes,

          s.tablespace_name, s.extents, s.initial_extent

    FROM   dba_lobs l, dba_segments s

    WHERE  s.segment_name = l.index_name

    AND    s.owner = l.owner

    AND    s.segment_type = 'LOBINDEX'

  )

  WHERE owner = UPPER('&owner')

)

WHERE total_table_meg > 10

ORDER BY total_table_meg DESC, meg DESC

/

Day to Day Commands

Day to Day Commands

day2day commands

Different Ways To Check The Tablespaces Size

Different Ways To Check The Tablespace Size

 

Script 1.

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a25
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15

SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

 

Script 2.

 

set lines 135
column TABLESPACE_NAME for a20
column FILE_NAME for a93
set lines 220 pages 220

 SELECT df.tablespace_name, df.size_mb, f.free_mb, df.max_size_mb, f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb, df.file_name FROM   (SELECT file_id, file_name, tablespace_name, TRUNC(bytes/1024/1024) AS size_mb, TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb FROM   dba_data_files) df, (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb, file_id FROM dba_free_space GROUP BY file_id) f WHERE  df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name;

                                 

Script 3.                                 

                                 

select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name order by 1;

  

Script 4.  

 

set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30

select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)", round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT, lower(vc.name) as container from (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f, (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s, v$containers vc where f.con_id=s.con_id(+) and f.tablespace_name=s.tablespace_name(+) and f.con_id=vc.con_id order by container, tbspce;

 

Script 5.

 

set pages 999
set lines 400

SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size,round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,round(df.bytes / (1024 * 1024), 2) curr_ts_size,round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_data_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes UNION ALL SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size, round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2) curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM (select tablespace_name, bytes_used bytes from V$temp_space_header group by tablespace_name, bytes_free, bytes_used) fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_temp_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes ORDER BY 4 DESC;

Oracle SQL Developer Shortcuts

Oracle SQL Developer Shortcuts

  

Administering the DDL Log Files in 12c

Administering the DDL Log Files in 12c

The DDL log is created only if the ENABLE_DDL_LOGGING initialization parameter is set to TRUE. When this parameter is set to FALSE, DDL statements are not included in any log. A subset of executed DDL statements is written to the DDL log.

How to administer the DDL Log?

--> Enable the capture of certain DDL statements to a DDL log file by setting ENABLE_DDL_LOGGING to TRUE.

--> DDL log contains one log record for each DDL statement.

--> Two DDL logs containing the same information:

--> XML DDL log: named log.xml

--> Text DDL: named ddl_<sid>.log

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER

ALTER/CREATE/DROP FUNCTION

ALTER/CREATE/DROP INDEX

ALTER/CREATE/DROP OUTLINE

ALTER/CREATE/DROP PACKAGE

ALTER/CREATE/DROP PACKAGE BODY

ALTER/CREATE/DROP PROCEDURE

ALTER/CREATE/DROP PROFILE

ALTER/CREATE/DROP SEQUENCE

CREATE/DROP SYNONYM

ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE

ALTER/CREATE/DROP TRIGGER

ALTER/CREATE/DROP TYPE

ALTER/CREATE/DROP TYPE BODY

DROP USER

ALTER/CREATE/DROP VIEW

Example

$ more ddl_orcl.log

Thu Nov 15 08:35:47 2012

diag_adl:drop user app_user

Locate the DDL Log File

$ pwd

/u01/app/oracle/diag/rdbms/orcl/orcl/log

$ ls

ddl ddl_orcl.log debug test

$ cd ddl

$ ls

log.xml

Notes: 

- Setting the ENABLE_DDL_LOGGING parameter to TRUE requires licensing the Database Lifecycle Management Pack.

- This parameter is dynamic and you can turn it on/off on the go.

- alter system set ENABLE_DDL_LOGGING=true/false;

How to set oracle path in Linux

How to set oracle path in Linux 

[oracle@test04 ~]$vi MQTEST.env 

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:.

export ORACLE_SID=MQTEST

ALERT=/u01/app/oracle/diag/rdbms/mqprod/MQTEST/trace

:wq!

dbms_system.ksdwrt–Write messages to Oracle alert log

dbms_system.ksdwrt–Write messages to Oracle alert log

Oracle provides a procedure to insert messages to the alert log and/or trace files for testing/development purposes. This can be used to check the effectiveness of the monitoring tools/scripts used in the environment, to understand how well the monitoring tool captures the messages in the alert log.

Usage:

SQL> exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');

PL/SQL procedure successfully completed.

SQL>

How to search a particular query in oracle ?

How to search a particular query in oracle ?

select table_name from dict where table_name like '%LINK%';

select table_name from dict where table_name like '%DIRECTORY%';

select table_name from dict where table_name like '%AUDIT%';

Database Monitoring

Database Monitoring 

Database and Instance Last start time:

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
SELECT SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from  sys.v_$session where  sid=1;

Track Database Version:

SELECT * from v$version;

Track Database Name and ID information:

select dbid, name from v$database;

Track Database Global Name information:

select * from global_name;

Track Database Instance name:

SELECT INSTANCE_NAME FROM V$INSTANCE;‎

Track Database Host Details:

SELECT UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;

Display information about database services:

select name,  network_name from  dba_services order by name;

Track Database Present Status:

SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;

DB Character Set Information:

select * from nls_database_parameters;

Track Database default information:

select username, profile, default_tablespace, temporary_tablespace from dba_users;

How to set a setnewname

select 'set newname for datafile '||file#||' to NEW;' from v$datafile;
-- select 'set newname for datafile '||file#||' to /newpath/NEW;' from v$datafile;