sql profile - ORACLEAGENT BLOG

Get installed sqlpatches in database

— From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
(or)
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,action, status, description,version,patch_id, bundle_series FROM sys.dba_registry_sqlpatch;

— For 11g and below:
set lines 2000
select * from dba_registry_history;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

GET INDEX USAGE

—Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
— First enable monitoring usage for the indexes.

alter index INDEX_NAME.S_ASSET_TEST monitoring usage;

–Below query to find the index usage:

select * from v$object_usage;

Get sql query to html format

— We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “EMPLOYEE REPORT –

–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
spool report.html
Select * from scott.emp;
spool off
exit

row_count of all the tables of a schema

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype(‘select /+ PARALLEL(8) */ count() cnt from “&&SCHEMA_NAME”.’||table_name),’/ROWSET/ROW/CNT’))
rows_in_table from dba_TABLES
where owner=’&&SCHEMA_NAME’;

Disable or enable all triggers of schema

BEGIN
FOR j IN (SELECT trig_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE ‘ALTER TRIGGER ‘ || i.trig_name || ‘ DISABLE’;
END LOOP;
END;
/

Get size of the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
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
/

Oracle E-Business Suite Installation EBS 12.2.0

Oracle E-Business Suite Installation (EBS 12.2.0).

Now i am going to install Oracle E-Business Suite Installation (EBS),For this I have already created an instance in my cloud account.

I have created an instance in the following specificaions as mentioned below,

OS Version: Oracle Linux 7.9

Shape: VM Standard E 2.4, 4 core CPU and 32 GB Memory.(depends of the CPU, our EBS installation software running time is decided)

Instance name: ANDHRA

Once the instance is created we can proceed with the installation procedures.

Then connect with putty login connect as root user.

Better install the software using Vnc ,it is more advantage because while we are installing the software if any power outage occurs it doesn’t disturbs the installation.

if you are going to use vnc install the following packages,

yum install *gnome*
yum install *vnc*
yum install *desktop*

Stop the firewall using the below command systemctl stop firewall.

Then set the selinux parameter as permissive which is in the vi /etc/selinux/config location.

After that you have to reset the system using init 6 or setenforce 0

Add the following set of lines to the files.

vi /etc/sysctl.conf

vi /etc/security/limits.conf

Next add the groups and users,here i am adding oracle and applmgr users to the newly created dba group.

set the passwords for the oracle and applmgr user.

Examine the locations of ora inventory and change the mod and group of orainst.loc

Create the below mentioned directories that is needed to install the software.

Add and attach the block volumes(here i am adding block volume size of 700GB) after attaching the block volume,view the block volume details and click ISCSI commands and information,once you click that there will be the informations for connecting and disconnecting.

Do the partition for the newly added block volume.Simply just attaching the block volume will not be partitioned.

Download the ebs software version that we are going to install as wget file.And move the file to the EBS directory in the /u01/stage mountpoint where we are going to unzip the software.

Run the given pre install,

yum install oracle-ebs-server-R12-preinstall     --------------------------it is for ebs.
yum install oracle-database-server-12cR2-preinstall ------------------it is for database.

unlink /usr/lib/libXtst.so.6 and link libXtst.so.6.1 with /usr/lib.it is important because it is helpful to load the frontend page.

we are going to create the stage area,then specify the directory which contains the zipped installation media.

Move the downloaded software (ie. wget.sh file) to the stage area location to perform installation.

Here starts the rapid installation process.

In this choose Install Oracle E-business Suite Release 12.2.0

Here provides you a option to receive security updates via My Oracle Support if you want enable the check Box and provide the mail Email ID.

in this window it opts for configuration choice either we can create a new configuration or Load the saved configuration.

Here we can see Global System Settings leave it by default as it is don’t change any values in this window.

Now only we are going to configure the Database Node Configuration,set up those parameters such as database sid,database host name,db operating system.

choose the Licensing Type , suite Licensing or Component Licensing.I have choosen suite licensing.

It shows the License Additional Products,don’t do any change,click Next.

In this choose country out of the available regions which is displayed.

select the character set by specifying the language you are going to use.I choose the language as American_English – US.

Next we have to configure the Primary Applications Node Configuration,such as setting the primary host name,operating system,Apps OS User,Apps OS Group,Apps Base Dir,Apps Instance Dir

Provide the Application User Information such as WLS Admin User,Password and proceed further for Installation.

Continue with the Node Information.Database node,primary apps node information should be given here.

Rapid Install Wizard is performing the system checks,once it is fine we can move further.

It shows the list of pre-install tests if and only it satisfies all the condition we can go with the installation process.For us the instance passed all of the pre-install tests.

we are set with the process,installation is in progress it takes some time to install the software.

Depending upon the CPU core that we have specified in the instance creation the installation time may differ.