Oracle biggest Object size wise

Oracle Database 10  biggest Object size wise

set pagesize 200
col segment_name format a30
col tablespace_name format a30
select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, MB from
(select segment_name, segment_type, tablespace_name,bytes/1024/1024 MB, rank() over(order by bytes desc) srank from dba_segments) where srank <=10;
 

                                        Tablespace           Allocated
SEGMENT_NAME         SEGMENT_TYPE       Name                        MB
-------------------- ------------------ -------------------- ---------
IDL_UB1$             TABLE              SYSTEM                     240
SOURCE$              TABLE              SYSTEM                      64
SYS_LOB0000056487C00 LOBSEGMENT         SYSAUX                      57
025$$

IDL_UB2$             TABLE              SYSTEM                      30
SYS_LOB0000062550C00 LOBSEGMENT         SYSAUX                      26
006$$

C_TOID_VERSION#      CLUSTER            SYSTEM                      23
SYS_LOB0000064027C00 LOBSEGMENT         SYSAUX                      14
004$$

_SYSSMU5_538557934$  TYPE2 UNDO         UNDOTBS1                    14
JAVA$MC$             TABLE              SYSTEM                      13
CUSTOMERS            TABLE              EXAMPLE                     12
I_SOURCE1            INDEX              SYSTEM                      12

11 rows selected.

Oracle Temp Tablespace info

Oracle Temp Tablespace info
Oracle Temp Tablespace

Oracle Temp File usage Info  ...

set lines 300
COLUMN mb_total        HEADING 'Total|MB'        FORMAT 99,999
COLUMN mb_used         HEADING 'Used|MB'         FORMAT 99,999
COLUMN mb_free         HEADING 'Free|MB'         FORMAT 99,999
column FILE_NAME       Heading 'File|Name'       FORMAT a30
column tablespace_name Heading 'Tablespace|Name' FORMAT a10
column TABLESPACE      Heading 'Tablespace|Name' FORMAT a10
column MB              Heading 'Allocated|MB'    FORMAT 99,999
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, A.tablespace_name TABLESPACE, D.mb_total,SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024 mb_free FROM dba_temp_files dbatf, v$sort_segment A,
(SELECT B.name, C.block_size,  SUM (C.bytes)/1024/ 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size   ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
D.mb_total, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
/



File                           Allocated Tablespace   Total    Used    Free
Name                                  MB Name            MB      MB      MB
------------------------------ --------- ---------- ------- ------- -------
+DATA/orcl/temp01.dbf                 29 TEMP            29       0      29

ORA-16649: possible failover to another database prevents this database from being opened



SQL> startup;
ORACLE instance started.

Total System Global Area  577511424 bytes
Fixed Size                  1338000 bytes
Variable Size             461374832 bytes
Database Buffers          109051904 bytes
Redo Buffers                5746688 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened


SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY

SQL> alter system set dg_broker_start=false scope=both sid='*';  ---- do this
System altered.

SQL> alter database open;
Database altered.