How to find table size?

Hello, if you want to see all table sizes in oracle database you can use this code. SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') 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 … Continue reading How to find table size?

Advertisements

Error 45 initializing SQL*Plus

My command : $nohup sqlplus "/ as sysdba" @script.sql & Error Messages : Error 45 initializing SQL*Plus Solutions: Go to end of the script, You have to add another line with a lone "/" after it, or you will get this error. Bug 3039738 - Error 45 Initializing Sql*Plus Running A Shell Script    

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Problem : ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Solutions : + (Check the datafile size) Show Oracle Session; SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND … Continue reading ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Compiles all invalid views for specified schema

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'VIEW' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compiles all invalid views for specified schema

Compile all İnvalid Trigger

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'TRIGGER' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compile all İnvalid Trigger

Compile all invalid Procedure

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;' FROM all_objects a WHERE a.object_type = 'PROCEDURE' AND a.status = 'INVALID' AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1')); SPOOL OFF -- Comment out following line to prevent immediate run @temp.sql SET PAGESIZE … Continue reading Compile all invalid Procedure

How to create a database link in Oracle?

The syntax has been enhanced to remove the need to epscify a TNS service_name: CREATE PUBLIC DATABASE LINK <Link_Name>  CONNECT TO <remote_username> IDENTIFIED BY <PWD> USING ''tns_service_name';