important Database query

To query all database tables in given database owned by the user T24

SQL> l
  1* select table_name, tablespace_name from dba_tables where owner='T24'
SQL> select table_name, tablespace_name from dba_tables where owner='T24';

To query space usage

SQL>  select sum(bytes)/1024/1024/1000 gb from dba_data_files;

        GB
----------
   47.3125

SQL>

SQL> select sum(sum_bytes)/1024/1024 m_bytes
from(
select sum(bytes) sum_bytes from v$datafile
union
select sum(bytes) sum_bytes from v$tempfile
union
select (sum(bytes) * members) sum_bytes from v$log
group by members);  2    3    4    5    6    7    8

   M_BYTES
----------
   47927.5

SQL> l
  1  select sum(sum_bytes)/1024/1024 m_bytes
  2  from(
  3  select sum(bytes) sum_bytes from v$datafile
  4  union
  5  select sum(bytes) sum_bytes from v$tempfile
  6  union
  7  select (sum(bytes) * members) sum_bytes from v$log
  8* group by members)

To query database data guard status

SQL>  select GUARD_STATUS , DATAGUARD_BROKER, FLASHBACK_ON, REMOTE_ARCHIVE, PROTECTION_MODE from v$database;

GUARD_S DATAGUAR FLASHBACK_ON       REMOTE_A PROTECTION_MODE
------- -------- ------------------ -------- --------------------
NONE    DISABLED NO                 ENABLED  MAXIMUM PERFORMANCE

SQL>
SQL>

To query all export and import directory

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            XMLDIR
/ade/b/3705469564/oracle/rdbms/xml

SYS                            IMPDP_DIR
/u02/backup/export/CBELIVE

SYS                            DATA_PUMP_DIR
/u03/export/FEMOBILE


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state





SQL>
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH

XMLDIR
/ade/b/3705469564/oracle/rdbms/xml

IMPDP_DIR
/u02/backup/export/CBELIVE

DATA_PUMP_DIR
/u03/export/FEMOBILE