Posts

pdb queries

set lines 333 col tablespace_name for a30 col contents for a25 col status for a15 col con_name for a20 select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status from v$containers c, cdb_tablespaces t where c.con_id=t.con_id order by 1,2; ---------------------------------------------------------------------------------------------------------- set lines 400 col con_name for a20 col username for a25 col account_status for a28 -- col LOCK_DATE -- col EXPIRY_DATE -- col LAST_LOGIN col DEFAULT_TABLESPACE for a25 col TEMPORARY_TABLESPACE for a15 --col LOCAL_TEMP_TABLESPACE for a15 col PROFILE for a25 select c.con_id, c.name  con_name, u.username,u.account_status,u.common,u.default_tablespace,u.temporary_tablespace,u.profile from v$containers c, cdb_users u where c.con_id=u.con_id order by 1,2; ---------------------------------------------------------------------------------------------------------- set lines 400 col file_name for a66 col...

query

############################################################        DB QUERIES          ####################################################################### =================QUERY FOR TOTAL SIZE OF ORACLE DATABASE: SELECT ( SELECT SUM(BYTES)/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) + ( SELECT NVL(SUM(BYTES),0)/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +( SELECT SUM(BYTES)/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE) "SIZE IN GB" FROM DUAL; =================INDIVIDUALLY CHECKING ALL THE DATABASE FILES AS WELL AS NON-DATABASE  FILES: SET LINES 100 PAGES 999 COL NAME FORMAT A50 SELECT NAME, BYTES FROM (SELECT NAME, BYTES FROM V$DATAFILE UNION ALL SELECT NAME, BYTES FROM V$TEMPFILE UNION ALL SELECT LF.MEMBER "NAME", L.BYTES FROM V$LOGFILE LF , V$LOG L WHERE LF.GROUP# = L.GROUP# UNION ALL SELECT NAME, 0 FROM V$CONTROLFIL...

more about scheduler window

Installed by default, the database comes with three preconfigured Automatic Maintenance Tasks, called clients. First, the collection of optimizer statistics (Optimizer Statistics Collection), the Segment Advisor (Space Advisor) and the automatic SQL tuning (SQL Tuning Advisor). a. Status First, the query for the current state of the database in DBA_AUTOTASK_CLIENTS. Only in Enterprise Edition is the SQL Tuning Advisor ENABLED. SQL> select client_name, status, attributes from dba_autotask_client; CLIENT_NAME                        STATUS   ATTRIBUTES ---------------------------------  -------  ---------------------------------------- auto optimizer stats collection    ENABLED  ON BY DEFAULT , VOLATILE, SAFE TO KILL auto space advisor               ...