INSTALLATION DOCUMENTS BY RAVI

Showing posts with label ORACLE DATABASE. Show all posts
Showing posts with label ORACLE DATABASE. Show all posts

Sunday, April 19, 2020

SQL query to find top 50 tables by size

select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;


Note: Change 50 to any other number of tables you want to find

Friday, January 17, 2020

Status : Failure -Test failed: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Status: Failure -Test failed: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Check the LOGINS status with the below SQL command.





















Connect to the database as an SYS user through command prompt and execute the below commands











Grant restricted session privilege to the required user as below.






Sunday, July 21, 2019

Error in invoking target 'agent nmb nmo nmhs' of makefile


Error in invoking target 'agent nmb nmo nmhs' of makefile
















INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'agent nmb nmo nmhs' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2019-06-17_02-10-30AM.log' for details.
Exception Severity: 1

SOLUTION
The solution is to add “-lnnz11” to the end of the ins_emagent.mk
Edit $ORACLE_HOME/sysman/lib/ins_emagent.mk, search for the line
$(MK_EMAGENT_NMECTL)
 Replace the line with
$(MK_EMAGENT_NMECTL) -lnnz11
Then click “Retry” button to continue.










































Friday, May 31, 2019

Creating Pluggable database using database configuration assistant


Launch Database Configuration Assistant



























Select Manage Pluggable Databases and click on Next




















Select Create a Pluggable Database and click on Next




















Select database , provide username and password and click on Next





















Click on Yes





















Click on Next





















Provide pluggable database name, location and user details and click on Next





















Click on Yes





















Click on Finish







































Pluggable database created successfully, click on close



  Opatch reports 'Cyclic Dependency Detected' error when patching ODI   Issue: When applying a Patch Set Update (PSU) to WebLogic Se...