DBA Commands

Contents:- DBA Commands

Query to get default tablespace name

Sql> select property_value from database_properties where property_name=’DEFAULT_PERMANENT_TABLESPACE’;

Check Database Open with SPFILE or PFILE.

SELECT DECODE(VALUE,NULL,'PFILE','SPFILE') FROM SYS.V_$PARAMETER WHERE NAME='spfile';

Database Total Size (By using SQLQUERY)

SELECT (a.data_size + b.temp_size + c.redo_size + d.controlfile_size) / 1024
          "total_size in GB"
  FROM (SELECT SUM (bytes) / 1024 / 1024 data_size FROM dba_data_files) a,
       (SELECT NVL (SUM (bytes), 0) / 1024 / 1024 temp_size
          FROM dba_temp_files) b,
       (SELECT SUM (bytes) / 1024 / 1024 redo_size FROM sys.v_$log) c,
       (SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024
                  controlfile_size
          FROM v$controlfile) d;

Check How Many users connected to the database.

SELECT username,
         osuser,
         terminal,
         UTL_INADDR.get_host_address (terminal) IP_ADDRESS
    FROM v$session
   WHERE username IS NOT NULL
ORDER BY username, osuser;