Sql

Oracle Sql output format

I am using the following form for regular sql output


SET
LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000; 

for example show Oracle Asm disks status :

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;
output like this.

er

Advertisements
Standard
Oracle, Troubleshooting

PL/SQL: could not find program unit being called– Errors

hello, if you are executing Plsql scripts sometimes you can see this errors.
PL/SQL: could not find program unit being called.

Solutions : Check that all packages, procedure, Functions,programs, including their package bodies is invalid.  You must change status all invalid object to valid. 

plsql> EXEC PCOMFLX_PR.MIGRATION.FULL_MIGRATION(2121);
Error : ORA-04063: package body “PCOMFLX_PR.MIGRATION” has errors
ORA-06508: PL/SQL: could not find program unit being called: “PCOMFLX_PR.MIGRATION”
ORA-06512: at line 1

Is it  PCOMFLX_PR.MIGRATION packages invalid ? yes.

ee

or : show all invalid object :

select
owner c1,
object_type c3,
object_name c2
from
dba_objects
where
status != ‘INVALID’
order by
owner,
object_type;

have a good day.

Standard
Oracle

Oracle Database File System Layout

File System Layout

When a database deployment is created using the Oracle Database Cloud Service service level, Oracle Cloud Service storage volumes are created and allocated as follows.

File System Mount Description
swap Swap space; 4 GB allocated from the boot Compute Cloud storage volume.
/ (root) Operating system files; 25.5 GB allocated from the boot Compute Cloud storage volume.
/boot Operating system kernel; 500 MB allocated from the boot Compute Cloud storage volume.
/u01 Oracle product software; the entire bits Compute Cloud storage volume.
/u02 Oracle Database data storage; the entire data Compute Cloud storage volume.
/u03 Database backup storage; the entire fra Compute Cloud storage volume.
/u04 Database redo logs; the entire redo Compute Cloud storage volume.

referances : https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/troubleshoot-this-service.html

Standard
Troubleshooting

XSHELL – “No matching outgoing encryption algorithm found”

Hello,  I use xshell 5 connect to oracle database (open sshd) , but  while connecting oracle db i saw  message show “No matching outgoing encryption algorithm found” errors.

So basic this problem solutions.

please step by step follow this slayt

abstract :
Go to the session properties > SSH > Security and click on the Edit button. Select everything in the Cipher list.

This slideshow requires JavaScript.

Try connecting to the server again. Have a good day.

Standard
Linux

UDI-27140: operation generated ORACLE error 27140 Error Solutions

Hello,  UDI-27140: operation generated ORACLE error 27140 error

solutions:

connect to root  ( Oracle home and Grid home )

chmod 6751 /$ORACLE_HOME/bin/oracle
chmod 6751  /oracle/product/11.2.0.4/grid/bin/oracle

 

have a good day.

 

Standard
Oracle

AWR Report on the Oracle Database

Hello, to get awr report :

connect to oracle db as sysdba 
percula40: / home / oracle # sqlplus / as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Tue Mar 27 13:44:46 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
run this scripts:
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

He will ask the following questions with the order;

1. Enter value for report_type: -> I enter the report form, "HTML".
2. Enter value for num_days: -> How many days do you want to get snapshots, I enter "30".
3. Enter value for begin_snap: -> Look at the list and enter the initial snapshot. >>>> Snap Id
4. Enter value for end_snap: -> Look at the list and enter end snapshot. >>>>>> Snap Id
5. Enter value for report_name: -> I give the report a name, for example awr_report.hmtl .

Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
ProdTTdev  85583 21 Mar 2018 04:00 1
85584 21 Mar 2018 04:30 1 >> SnapID=85584
85585 21 Mar 2018 05:00 1
85586 21 Mar 2018 05:30 1
85587 21 Mar 2018 06:00 1
85588 21 Mar 2018 06:30 1
85589 21 Mar 2018 07:00 1
85590 21 Mar 2018 07:30 1
85591 21 Mar 2018 08:00 1
85592 21 Mar 2018 08:30 1
85593 21 Mar 2018 09:00 1
85594 21 Mar 2018 09:30 1
85595 21 Mar 2018 10:00 1
85596 21 Mar 2018 10:30 1
85597 21 Mar 2018 11:00 1
85598 21 Mar 2018 11:30 1
85599 21 Mar 2018 12:00 1
85600 21 Mar 2018 12:30 1
85601 21 Mar 2018 13:00 1
85602 21 Mar 2018 13:30 1
85603 21 Mar 2018 14:00 1
85604 21 Mar 2018 14:30 1
85605 21 Mar 2018 15:00 1
85606 21 Mar 2018 15:30 1
85607 21 Mar 2018 16:00 1
85608 21 Mar 2018 16:30 1
85609 21 Mar 2018 17:00 1
85610 21 Mar 2018 17:30 1
85611 21 Mar 2018 18:00 1
85612 21 Mar 2018 18:30 1
85613 21 Mar 2018 19:00 1
85614 21 Mar 2018 19:30 1
85615 21 Mar 2018 20:00 1
85616 21 Mar 2018 20:30 1
85617 21 Mar 2018 21:00 1
85618 21 Mar 2018 21:30 1
85619 21 Mar 2018 22:00 1
85620 21 Mar 2018 22:30 1
85621 21 Mar 2018 23:00 1
85622 21 Mar 2018 23:30 1
85623 22 Mar 2018 00:00 1

Show Oracle AWR reports locations:

awr report under the /home/oracle directory. have a good day.

Standard
Troubleshooting

Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

hello, I am getting an ORA-01000 SQL exception.

problem description:

Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError (T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive (T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC (T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall .__ AW_doOALL (T4C8Oall.java:531)
at oracle.jdbc.driver.T4C8Oall.doOALL (T4C8Oall.java)
at oracle.jdbc.driver.T4CCallableStatement .__ AW_doOall8 (T4CCallableStatement.java:204)
at oracle.jdbc.driver.T4CCallableStatement.doOall8 (T4CCallableStatement.java)

solution:  connect to db as sysdba than run this query:

Sql> show parameter cursor;

output :

cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 1500
session_cached_cursors integer 50

open_cursors integer size 1500,we need to change this value.
for example 2500 

Sql>alter system set open_cursors = 2500 scope=both;
system altered.

sql >SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = ‘opened cursors current’ and p.name= ‘open_cursors’ group by p.value;
or :
Sql> show parameter cursor;

output :

cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 2500
session_cached_cursors integer 50

problem is solved. Have a good day.

Standard