Please find the Quick General query as per your requirement

Please find the Quick General query as per your requirement

Find the status of the database

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select database_status from v$instance;

DATABASE_STATUS

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

ACTIVE

Or – To get the complete details of DB

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO

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

1 testdb

testdb.local

11.2.0.4.0 17-AUG-17 OPEN NO 1 STARTED

ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

Find the uptime of the database using sqlplus

SQL> select 'Hostname : ' || host_name ,'Instance Name : ' || instance_name ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' || trunc( 24*((sysdate-startup_time) - trunc(sysdate-startup_time))) || ' hour(s) ' || mod(trunc(1440*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' minute(s) ' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' seconds' uptime from sys.v_$instance;

'HOSTNAME:'||HOST_NAME

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

'INSTANCENAME:'||INSTANCE_NAME STIME

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

UPTIME

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

Hostname : testdb.local

Instance Name : testdb Started At : 17-AUG-2017 16:02:08

Uptime : 33 days(s) 23 hour(s) 39 minute(s) 50 seconds

SQL> SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') FROM v$session WHERE sid=1;

TO_CHAR(LOGON_TIME,

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

17/08/2017 16:02:10

Find the Complete size of the database usage

SQL> col "Database Size" format a20

SQL> col "Free space" format a20

SQL> col "Used space" format a20

SQL> select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Database Size Used space Free space

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

46 GB 39 GB 7 GB

Find the dblink information as shown below:

SQL> SELECT DB_LINK, USERNAME, HOST FROM ALL_DB_LINKS;

DB_LINK USERNAME HOST

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

MYLINK MIR REMOTEDB

Find the IO progress of the query

SQL>select * from v$sess_io;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES OPTIMIZED_PHYSICAL_READS

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

1 0 0 0 0 0 0

2 0 0 0 0 0 0

3 0 0 0 0 0 0

4 8267 24801 0 0 0 0

Find the nls database set

SQL> select * from nls_database_parameters;

PARAMETER VALUE

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

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET AL32UTF8

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE

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

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 11.2.0.4.0

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE

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

AL32UTF8

Find the privilege of the users in the oracle database:

SQL> select GRANTEE, PRIVILEGE , ADMIN_OPTION from dba_sys_privs where GRANTEE='MIR';

GRANTEE PRIVILEGE ADM

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

MIR UNLIMITED TABLESPACE NO

Note: If you omit where clause, you can view all the users

Find the auto extend tablespaces:

SQL> select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files;

SUBSTR(FILE_NAME,1,50) AUTO

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

/u01/app/oracle/oradata/prim/users01.dbf YES

/u01/app/oracle/oradata/prim/undotbs01.dbf YES

/u01/app/oracle/oradata/prim/sysaux01.dbf YES

SQL> select 'alter database datafile '|| file_name || ' '| | ' autoextend on maxsize unlimited;' from dba_data_files;

'ALTERDATABASEDATAFILE'||FILE_NAME||''||'AUTOEXTENDONMAXSIZEUNLIMITED;'

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

alter database datafile /u01/app/oracle/oradata/prim/users01.dbf autoextend on maxsize unlimited;

alter database datafile /u01/app/oracle/oradata/prim/undotbs01.dbf autoextend on maxsize unlimited;

alter database datafile /u01/app/oracle/oradata/prim/sysaux01.dbf autoextend on maxsize unlimited;

Etc...

Find the date wise archive log generated in a oracle database:

SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

TIME SIZE_MB

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

02-SEP-17 174.628906

03-SEP-17 196.592773

04-SEP-17 140.374023

05-SEP-17 175.258789

06-SEP-17 148.604492 ….

Find the existing created in database

SQL> select * from all_objects where object_type ='DIRECTORY';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

SYS ORACLE_OCM_CONFIG_DIR 13744 DIRECTORY 24-AUG-13 20-JAN-16 2016-01-17:05:53:28 VALID N N N 9

SYS DUMPFILE 53019 DIRECTORY 17-AUG-16 28-AUG-16 2016-08-17:08:08:59 VALID N N N 9

SYS NEWDUMP 153291 DIRECTORY 28-AUG-16 28-AUG-16 2016-08-28:03:43:07 VALID N N N 9

SQL> SELECT owner, directory_name, directory_path FROM all_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

SYS DAILY_DUMP_BACKUP /backup/daily_dump_backup

SYS DUMPFILE_DB /tmp/dumpfile_db

SYS TABLE_MPG /tmp/table_mpg

Find the active session in oracle database

SQL> SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status='ACTIVE' AND UserName IS NOT NULL;

SID SERIAL# USERNAME STATUS SCHEMANAME LOGON_TIM

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

719 4983 SYS ACTIVE SYS 18-SEP-17

Find the count of session is active in database

SQL> Select count (*) From V$session where status='ACTIVE';

COUNT (*)

----------

Find the individual user

SQL> select sid, serial#, status from v$session where username='MIR';

no rows selected

Find the no of process in database

SQL> select username, program from v$process;

USERNAME PROGRAM

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

PSEUDO

oracle oracle@ogg-test1 (PMON)

oracle oracle@ogg-test1 (PSP0)

oracle oracle@ogg-test1 (VKTM) ……

Find the process, stat and cpu usage

SQL> select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

USERNAME SID CPU_USAGE_SECONDS

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

SYS 719 2.41

SYS 719 0

SQL> select pname, pval1 from sys.aux_stats$;

PNAME PVAL1

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

STATUS

DSTART

DSTOP

FLAGS 1

CPUSPEEDNW 3074.07407

IOSEEKTIM 10

IOTFRSPEED 4096

SREADTIM

MREADTIM

CPUSPEED

MBRC

PNAME PVAL1

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

MAXTHR

SLAVETHR

Get the Sysdate, Time etc…

SQL> Select sysdate from dual;

SYSDATE

---------

18-SEP-17

SQL> select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') date_and_time from dual;

DATE_AND_TIME

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

18-09-2017 14:50:39

SQL> SELECT sessiontimezone FROM DUAL;

SESSIONTIMEZONE

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

+04:30

SQL> SELECT SYSTIMESTAMP(3) FROM dual;

SYSTIMESTAMP(3)

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

18-SEP-17 02.52.32.633 PM +04:30

Find out the audit information in Oracle database

SQL> Show parameter audit

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/prim/adu

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

Find the datadump jobs status

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYSTEM SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0 0 0

Find the database running with SPFILE or Not

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

Init F

------

SPFILE

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfiletestdb.ora

Find the redolog size currently used in Oracle database

SQL> select a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b..MEMBER as redolog_file_name,

(a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP# asc;

GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB

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

1 1 11108 NO CURRENT /u01/app/oracle/oradata/prim/redo01.log 50

2 1 11106 YES INACTIVE /u01/app/oracle/oradata/prim/redo02.log 50

3 1 11107 YES INACTIVE /u01/app/oracle/oradata/prim/redo03.log 50

Find the status of all the background process in Oracle database

SQL> select FUNCTION_ID, FUNCTION_NAME from v$iostat_function order by FUNCTION_ID;

FUNCTION_ID FUNCTION_NAME

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

0 RMAN

1 DBWR

2 LGWR

3 ARCH

4 XDB

5 Streams AQ

6 Data Pump

7 Recovery

8 Buffer Cache Reads

9 Direct Reads

10 Direct Writes

FUNCTION_ID FUNCTION_NAME

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

11 Smart Scan

12 Archive Manager

13 Others

14 rows selected.

Find the status of the user password expired in oracle database

USERNAME ACCOUNT_STATUS

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

FLOWS_FILES EXPIRED & LOCKED

MDSYS EXPIRED & LOCKED

ORDSYS EXPIRED & LOCKED

EXFSYS EXPIRED & LOCKED

WMSYS EXPIRED & LOCKED Etc……

Find the size of the asm disk group using sqlplus

SQL> select GROUP_NUMBER,TOTAL_MB,FREE_MB from v$ASM_DISKGROUP;

Find the blocking session in database

SQL> SELECT

s.blocking_session,

s.sid,

s.serial#,

s.seconds_in_wait

FROM

v$session s

WHERE

blocking_session IS NOT NULL;

no rows selected

Find the lock session in database

SQL> select * from v$lock where block=1;

no rows selected

SQL> select count(*) from gv$lock where block=1;

COUNT(*)

----------

Find the status of the Oracle 12c database

SQL> select name, cdb, con_id from v$database;

NAME CDB CON_ID

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

ORA12C YES 0

SQL> select instance_name, status, con_id from v$instance;

INSTANCE_NAME STATUS CON_ID

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

ora12c OPEN 0

SQL> select NAME, OPEN_MODE from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDBORCL READ WRITE

PDB2 READ WRITE

Find out which SQL Query is currently in waiting stage

SQL> select

sid,

sql_text

from

v$session s,

v$sql q

where

sid in

(select

sid

from

v$session

where

state in ('WAITING')

and

wait_class != 'Idle'

and

event='enq: TX - row lock contention'

and

(q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

no rows selected

Find out the estimated size for sga assign in database

SQL> select estd_db_time_factor,sga_size ,sga_size_factor from v$sga_target_advice order by sga_size;

ESTD_DB_TIME_FACTOR SGA_SIZE SGA_SIZE_FACTOR

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

12.6709 1176 .5

1.8269 1764 .75

1 2352 1

.99 2940 1.25

.9892 3528 1.5

.9892 4116 1.75

.9892 4704 2

7 rows selected.

SQL> desc v$sga_target_advice

Name Null? Type

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

SGA_SIZE NUMBER

SGA_SIZE_FACTOR NUMBER

ESTD_DB_TIME NUMBER

ESTD_DB_TIME_FACTOR NUMBER

ESTD_PHYSICAL_READS NUMBER

Find the ASM Disk group usuage

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 5114 5055 0 5055 0 N CRS/

MOUNTED EXTERN N 512 4096 1048576 5114 1796 0 1796 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 5114 1696 0 1696 0 N FRA/

Sql> SELECT g.group_number "Group" , g.name "Group Name" , g.state "State" , g.type "Type" , g.total_mb/1024 "Total GB" , g.free_mb/1024 "Free GB" , 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" , 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" , 100*(min(d.free_mb/d.total_mb)) "MinFree" , 100*(max(d.free_mb/d.total_mb)) "MaxFree" , count(*) "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1;

SQL> set pagesize 2000

set lines 2000

set long 999

col path for a54

select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk;SQL> SQL> SQL> SQL>

NAME PATH HEADER_STATU FREE_MB READ_MB WRITE_MB

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

CRSVOL1 ORCL:CRSVOL1 MEMBER 5114 2 213

DATAVOL1 ORCL:DATAVOL1 MEMBER 5114 6876 3123

FRAVOL1 ORCL:FRAVOL1 MEMBER 5114 88 2684

ORCL:DATA1 PROVISIONED 0

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage

FROM v$asm_diskgroup; 2

NAME FREE_MB TOTAL_MB PERCENTAGE

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

CRS 5055 5114 98.8463043

DATA 1795 5114 35.0997262

FRA 1658 5114 32.4208056

Find the archive generate by hours in database

SQL> set lines 299
SQL> SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

DG Date 12AM 01AM 02AM 03AM 04AM 05AM 06AM 07AM 08AM 09AM 10AM 11AM 12PM 1PM 2PM 3PM 4PM 5PM 6PM 7PM 8PM 9PM 10PM 11PM

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

Feb 12 0 0 0 2 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0

Feb 11 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0

Feb 10 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0

…….

Find the current users are logged in database

SQL> select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b,

v$session s where a.statistic# = b.statistic# and s.sid=a.sid and

b.name = 'opened cursors current' and s.username is not null;

VALUE USERNAME SID SERIAL#

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

1 SYS 481 13025

Find the specific or all the user privilege in database

SQL> SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MIR';

GRANTEE PRIVILEGE

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

MIR UNLIMITED TABLESPACE

SQL> desc user_tab_privs;

Name Null? Type

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

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

HIERARCHY VARCHAR2(3)

SQL> desc all_tab_privs;

Find the entire user in database

SQL> select username from dba_users;

USERNAME

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

SPATIAL_WFS_ADMIN_USR

DIP

MDDATA

ORACLE_OCM

SPATIAL_CSW_ADMIN_USR

APEX_PUBLIC_USER

MIR ……………………………. Etc

Find the tablespace used by the user in database

SQL> select distinct TABLESPACE_NAME from dba_tables where owner='MIR';

TABLESPACE_NAME

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

GOLDENGATE

USERS

Find the invalid objects in database

SQL> SELECT owner,

object_name,

object_type,

status

FROM dba_objects

WHERE status ='INVALID';

SQL> desc dba_objects;

Find the size of table,index, schema in database

SIZE OF TABLE:

Select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’TABLE_NAME’ group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’TABLE_NAME’ group by owner,segment_name;

SIZE OF INDEX:

select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’INDEX_NAME’ group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’INDEX_NAME’ group by owner,segment_name

SIZE OF A USER/SCHEMA:

select owner, segment_name, sum(bytes)/1024/1024/1024 from dba_segments where owner = 'XRM' group by owner,segment_name;

Lots more to add……

Search This Blog

Mir Sayeed Hassan Blog- Oracle DBA

Please find the Quick General query as per your requirement - Tested & Verified

Comments

Post a Comment

Popular posts from this blog

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

Solutions: error while starting of Oracle Single instance ASM Database