Find the Quick General query as per your requirement
Posted by Mir Sayeed Hassan on October 4th, 2017
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
Or
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
Or
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
Or
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
Or
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
Or
Find the count of session is active in database
SQL> Select count (*) From V$session where status='ACTIVE'; COUNT (*) ---------- 49 Or
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
Or
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 mp 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
Or
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; GROUP_NUMBER TOTAL_MB FREE_MB ------------ ---------- ---------- 1 11072 1204 2 57192 5724
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
or
SQL> select count(*) from gv$lock where block=1; COUNT(*) ---------- 0
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
SQ> 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.
Or
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/
Or
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;
Or
SQL> set pagesize 2000 Set lines 2000 set long 999 col path for a54 SQL> 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; 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
Or
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> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MIR'; GRANTEE PRIVILEGE ------------------------------ ---------------------------------------- MIR UNLIMITED TABLESPACE
Or
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)
Or
SQL> desc all_tab_privs;
Find the entire user in database
SQL> select username from dba_users; USERNAME ------------------------------ SPATIAL_WFS_ADMIN_USR DIP SH IX 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';
Or
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;
Or
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;
Or
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……