Skip to main content

Script for checking db info

By



spool dbinfo.1st
set linesize 300;
set pagesize 1000;
set wrap off;
set numwidth 20
set echo off;
break on today
break on report
column today noprint new_value xdate
select TRUNC(sysdate) today from dual;
column instance_name noprint new_value instancename
select instance_name from v$instance;
column startup_time heading 'Ran|Since'
column instance heading 'Instance|Name'
column host_name heading 'Host|Name'
column platform_name heading 'Platform'
column created heading 'Created|Since'
column log_mode heading 'Log|Mode'
column 'index partion' heading 'Index|Partion'
column 'table partition' heading 'Table|Partition'
column 'materialized view' heading 'Materialize|View'
column platform_name format a20
column version format a10
column username format a20
column object_type format a20
column num_of_objects format 999,999
column owner format a20
column host_name format a20
column tablespace_name format a30
column object_name format a30
column owner format a20
column '#line Procedure' heading '#Line|Procedure'
column '#line function' heading '#Line|Function'
column '#line trigger' heading '#Line|trigger'
column '#line package body' heading '#Line Package|Body'
column db_link format a20
column host format a30
column what format a30
column interval format a20
column segment_name format a30

break on owner

select instance_name instance,host_name,version,created, startup_time,log_mode from v$database,v$instance;


prompt "---Display Total Database Size in Megs.---"
select sum(bytes)/1024/1024 "DATABASE SIZE in M" from dba_data_files;

Prompt "---Database Link---"
select owner,db_link,username,host from all_db_links;

Prompt "---Job Information---"
select job,schema_user,broken,next_date,interval,what from dba_jobs;

prompt "--- Tablespace: Size Information  ---" skip 1
break on report
compute sum of "total MB" on report
col "total MB" format 999,999,999,999,990
compute sum of "Free MB" on report
col "Free MB" format 999,999,999,999,990
compute sum of "Used MB" on report
col "Used MB" format 999,999,999,999,990


select
d.tablespace_name,
SUBSTR(d.file_name,1,50) "Datafile name",
ROUND(MAX(d.bytes)/1024/1024,2) as "total MB",
DECODE(SUM(f.bytes), null, 0, ROUND(SUM(f.Bytes)/1024/1024,2)) as "Free MB" ,
DECODE( SUM(f.Bytes), null, ROUND(SUM(f.Bytes)/1024/1024,2) , ROUND((MAX(d.bytes)/1024/1024) - SUM(f.bytes)/1024/1024,2)) as "Used MB"
from
DBA_FREE_SPACE f , DBA_DATA_FILES d
where f.tablespace_name (+) = d.tablespace_name
and f.file_id (+)= d.file_id
group by d.tablespace_name,d.file_name
order by 1;

set numwidth 7
prompt "---Display Object Type owned by Users---" skip 1
-- Detect # materialized view export will not export materialized views built from remote hosts
-- More preparation when perform import for materialized view used Rowid in old version
-- and Materialized view after import become tables
-- From here
break on report
compute sum of table on report;
compute sum of function on report;
compute sum of "package body" on report;
compute sum of trigger on report;
compute sum of index on report;

select owner,count(DECODE(OBJECT_TYPE,'TABLE',0)) AS "TABLE",
count(DECODE(OBJECT_TYPE,'TABLE PARTITION',0)) AS "TABLE PARTITION",
count(DECODE(OBJECT_TYPE,'PROCEDURE',0)) AS "PROCEDURE",
COUNT(DECODE(OBJECT_TYPE,'FUNCTION',0)) AS "FUNCTION",
COUNT (DECODE(OBJECT_TYPE,'PACKAGE BODY',0)) AS "PACKAGE BODY" ,
COUNT(DECODE(OBJECT_TYPE,'TRIGGER',0)) AS "TRIGGER",
COUNT(DECODE(OBJECT_TYPE,'LOB',0)) AS "LOB",
COUNT(DECODE(OBJECT_TYPE,'INDEX',0)) AS "INDEX" ,
COUNT(DECODE(OBJECT_TYPE,'INDEX PARTITION',0)) AS "INDEX PARTITION" ,
COUNT(DECODE(OBJECT_TYPE,'MATERIALIZED VIEW',0)) AS "MATERIALIZED VIEW" from all_objects
WHERE OWNER NOT IN ('SYS','SYSTEM')
group by owner;

set numwidth 20
Prompt '---Display total objects in the Database---'
select object_type,count(*) num_of_objects from dba_objects where owner not in ('SYS','SYSTEM') group by object_type;

Prompt '---Display the total lines of 20 largest Package body---'
select C.owner,object_name, "#LINE PACKAGE BODY" FROM
(SELECT A.OWNER, OBJECT_NAME,COUNT(DECODE(OBJECT_TYPE,'PACKAGE BODY',0)) AS "#LINE PACKAGE BODY" from all_objects A, all_source B
    where object_name=name
    and a.OWNER NOT IN ('SYS','SYSTEM')
    group by A.owner,OBJECT_NAME
    order by 3 desc) C
where rownum < 21;

--Prompt '---Display the total lines of 20 largest Procedure---'
--select C.owner,object_name, "#LINE PROCEDURE" FROM
--(SELECT A.OWNER, OBJECT_NAME,COUNT(DECODE(OBJECT_TYPE,'PROCEDURE',0)) AS "#LINE PROCEDURE" from --all_objects A, all_source B
--    where object_name=name
--    and a.OWNER NOT IN ('SYS','SYSTEM')
--    group by A.owner,OBJECT_NAME
--    order by 3 desc) C
--where rownum < 21;

Prompt '---Display the total lines of 20 largest TRIGGER---'
select C.owner,object_name, "#LINE TRIGGER" FROM
(SELECT A.OWNER, OBJECT_NAME,COUNT(DECODE(OBJECT_TYPE,'TRIGGER',0)) AS "#LINE TRIGGER" from all_objects A, all_source B
    where object_name=name
    and a.OWNER NOT IN ('SYS','SYSTEM')
    group by A.owner,OBJECT_NAME
    order by 3 desc) C
where rownum < 21;



col "MEGS" format 999,999,999,999,990.00
break on report
compute sum of MEGS on report;


Prompt '---Display Size of 20 Largest Tables---"
select OWNER,SEGMENT_NAME "TABLE", "MEGS"  from
(select OWNER,SEGMENT_NAME,BYTES/1024/1024 AS "MEGS" from dba_segments where segment_type='TABLE'
and owner not in ('SYS','SYSTEM') order by 3 desc)
where rownum<21;

Prompt '---Display Size of 20 Largest Indexes---"
select OWNER,SEGMENT_NAME "INDEX", "MEGS"  from
(select OWNER,SEGMENT_NAME,BYTES/1024/1024 AS "MEGS" from dba_segments where segment_type='INDEX'
and owner not in ('SYS','SYSTEM') order by 3 desc)
where rownum<21;

Prompt '---Display tablespaces that have equal or larger than 30 extents---'
SELECT   tablespace_name, segment_name, extents, max_extents, bytes,
         owner , segment_type
    FROM dba_segments
   WHERE extents >= 30
ORDER BY tablespace_name, owner, segment_type, segment_name;

Prompt '---Display tables that have more than 200 chain rows---'
select owner,table_name,num_rows,chain_cnt, avg_row_len, row_movement
from dba_tables where chain_cnt > 200
and owner not in ('SYS','SYSTEM','OUTLN');

Prompt '---How many CPU, Default DOP, Default = parallel_thread_per_cpu * cpu_cnt---'
select substr(name,1,30) name, substr(value,1,5) from v$parameter
where name in  ('parallel_threads_per_cpu' , 'cpu_count' );
Prompt '---How many tables a user have with different DOP's---'

select * from
 ( select  substr(owner,1,15)  Owner  , ltrim(degree) Degree, ltrim(instances)  Instances, count(*)   "Num Tables"  , 'Parallel' 
 from all_tables
 where ( trim(degree) != '1' and trim(degree) != '0' ) or        
( trim(instances) != '1' and trim(instances) != '0' )
 group by  owner, degree , instances
union
 select  substr(owner,1,15) owner  ,  '1' , '1' , count(*)  , 'Serial' 
 from all_tables where ( trim(degree) = '1' or trim(degree) != '0' )
  and  ( trim(instances) != '1' or trim(instances) != '0' )
 group by  owner )
order by owner;

Prompt '--- How many indexes a user have with different DOP's ---'

select * from
( select  substr(owner,1,15) Owner  , substr(trim(degree),1,7) Degree , substr(trim(instances),1,9) Instances ,count(*) "Num Indexes",         'Parallel' 
from all_indexes
where ( trim(degree) != '1' and trim(degree) != '0' )
or         ( trim(instances) != '1' and trim(instances) != '0' )
group by  owner, degree , instances
union
 select  substr(owner,1,15) owner  ,  '1' , '1' ,count(*)  , 'Serial' 
 from all_indexes where ( trim(degree) = '1' or trim(degree) != '0' )
 and         ( trim(instances) != '1' or trim(instances) != '0' )
group by  owner ) order by owner;

Prompt '---Detect obsolete parameters (Obsolete parameter might cause performance problems---'
select * from v$obsolete_parameter
where ISSPECIFIED != 'FALSE';

Prompt '---Display What options Current Database has---'
select substr(parameter,1,30) parameter, substr(value,1,30) value from v$option;

Prompt '---Display character Set---'
select substr(parameter,1,30) parameter, substr(value,1,30) value from v_$nls_parameters;

Prompt '---Display tables that has special data type which need more preparation during migration---'
select owner,table_name,data_type from all_tab_columns
where data_type in('BLOB','CLOB','CLOB','NCLOB','LONG','LONG RAW')
AND OWNER NOT IN('SYS','SYSTEM');

CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT ON FEEDBACK ON VERIFY ON
UNDEF today
TTITLE OFF
UNDEF OUTPUT

spool off;


 
Labels:

Comments

Post a Comment

Last Week Topics

How to break a bonded network interface red hat

By
1.- Bonding device called bond0 which aggregated by eth0 and eth1 # ifconfig bond0     Link encap:Ethernet  HWaddr 44:a8:42:5d:6d:5d           inet addr:192.168.1.51  Bcast:192.168.1.255  Mask:255.255.255.0           inet6 addr: fe80::5054:ff:fe4d:9004/64 Scope:Link           UP BROADCAST RUNNING MASTER MULTICAST  MTU:1500  Metric:1 eth0      Link encap:Ethernet  HWaddr 44:a8:42:5d:6d:5d           UP BROADCAST RUNNING SLAVE MULTICAST  MTU:1500  Metric:1 eth2      Link encap:Ethernet  HWaddr 44:a8:42:5d:76:29           UP BROADCAST RUNNING SLAVE MULTICAST  MTU:1500  Metric:1           RX packets:6 errors:0 dropped:0 overruns:0 frame:0 # cat /proc/net/bonding/bond0 Ethernet Channel Bonding Driver: v3.6.0 (September 26, 2009) Bonding Mode: fault-tolerance (active-backup) Primary Slave: em1 (primary_reselect always) Currently Active Slave: em1 MII Status: up MII Polling Interval (ms): 50 Up Delay (ms): 0 Down Delay (ms): 0 Slave Interface: eth0 MII Status: up Speed: 10000
Post a Comment
More Information »

HOW TO ADD A LINE WITH ANSIBLE WITH TAB SPACE IN A FILE

By
-bash-4.2# vi add_line_syslog.yml --- - name: script in order to add in the server list below with TAB spaces a Line in the SYSLOG CONF   hosts: oraking   tasks:     - name: Add a Line  in /etc/syslog.conf in order to delivery that information to another server       lineinfile:         path: /etc/syslog.conf         line: " *.err;auth.notice;auth.info;local0.info \t\t @10.10.10.200 "         insertbefore: ' \*.alert;kern.err;daemon.err '     - name: Restart system-log       command: /usr/sbin/svcadm restart svc:/system/system-log:default
Post a Comment
More Information »

How to configure publisher and install packages in Oracle Solaris 11

By
Connect to Oracle Support and Download the certificates Set proxy if you have one  #export https_proxy=https://usuario:password@191.118.2.110:8080  #export http_proxy=http://usuario:password@191.118.2.110:8080 You can add in the profile this information in order to have them configured in the login 1.-Download the ssl certicates in order to install them in the server 2.-Create a directory /var/pkg/ssl. #mkdir -p 755  /var/pkg/ssl 3.-Copy or Move the cerrtificates  # cd /var/pkg/ssl/  # ls -ltr total 53 -rw-r--r--   1 root     root        1679 Mar 27 13:36 Oracle_Solaris_11_Support.key.pem -rw-r--r--   1 root     root         932 Mar 27 13:36 Oracle_Solaris_11_Support.certificate.pem 4.- # pkg set-publisher \  -k /var/pkg/ssl/Oracle_Solaris_11_Support.key.pem \ -c /var/pkg/ssl/Oracle_Solaris_11_Support.certificate.pem \ -g https://pkg.oracle.com/solaris/support/ \ -G http://pkg.oracle.com/solaris/release/ solaris   5.-Verify the new publisher  # pkg publ
Post a Comment
More Information »

Updating Solaris 11.X to 11.3 and SRU to 11.3.2.4.0

By
Updating Solaris 11.X to 11.3 Download Repository Certificate s accessing with MOS user https://pkg-register.oracle.com/register/certificate/ Upload them to the server and rename as below pkg.oracle.com.certificate.perm pkg.oracle.com.key.perm root@:~# pkg unset-publisher This option below delete the currently repository and add the new one root@:~# pkg set-publisher -k  /root/pkg.oracle.com.key.pem -c /root/pkg.oracle.com.certificate.pem -G "*" -g https://pkg.oracle.com/solaris/support/ solaris root@:~# pkg publisher PUBLISHER                   TYPE     STATUS P LOCATION solaris                     origin   online F http://pkg.oracle.com/solaris/support/ root@# pkg update Validate previous boot environment root@:~# beadm list BE      Flags Mountpoint Space  Policy Created --      ----- ---------- -----  ------ ------- solaris NR    /          12.58G static 2016-03-15 07:11 root@:~# root@:~# PHASE             
1 comment
More Information »

What is PaaS, IaaS and SaaS?

By
  Infrastructure Platform as a Service (iPaaS) Integration Platform as a Service (iPaaS) is a suite of cloud services enabling the development, execution, and governance of integration flows connecting any combination of on-premises and cloud-based processes, services, applications, and data within individuals or across multiple organizations.   Ease of use Comprehensive integration of toolsets   Level of support   Readiness to support protocols Flexibility Ability to process, clean, and transform data in formats like XML and JSON; Performance when handling large-scale data operations and concurrent executions; Support for real-time processing and batch data integration; Monitoring for failures, latency, resource utilization, and workflow performance;   Security mechanisms for access control, data encryption, and single sign-on integrations   Infrastructure as a Service (IaaS)     I s a business model that delivers IT infrastructure like computing, storage, and network resources on a p
Post a Comment
More Information »

HOW TO SHARE WITH ZFS A FILE SYSTEM IN SOLARIS 11

By
As root execute the following Create the pool #zpool create ztemp c0d0 Create ZFS  #zfs create ztemp/temp Mount ZFS  #zfs set mountpoint=/temp ztemp/temp Share ZFS with the option "sharenfs=on" #zfs set sharenfs=on ztemp/temp Share and Select the PATH and SERVERS that you need that mount the ZFS from the NFS Server in our case the servers are oracle1,oracle2 and oracle3 with Read and Write Options #zfs set share=name=temp,path=/temporario,prot=nfs,anon=0,rw=oracle1:oracle2:oracle3 ztemp/temp
Post a Comment
More Information »

How to install Explorer Data Collector 8.11 Solaris 11

By
1.-Download from Oracle Support MOS the patch 22783063 and unzip and run the command below p22783063_8111638_SOLARIS64.zip #./install_stb.sh -verbose Extracting the STB payload ... Determining the check sums ... Sourcing STB library file ... List of Services Tool Bundle Components:    Oracle Explorer Data Collector 8.11    Oracle Serial Number in EEPROM (SNEEP) 8.11    Service Tag (ST) packages    Oracle Autonomous Crashdump Tool 8.17 (ACT) Would you like to (I)nstall, (X)tract, or (E)xit ? (I by default) X  <-----Select Extract (X) Extracting components for Solaris 11/sparc ...  Extracting IPS repository  Extracting SVR4 packages for Service Tag (ST) packages  - Package 5.11_sparc/SUNWsthwreg.sparc.5.10.pkg extracted Extraction to /var/tmp/stb/extract done Removing STB installation area ...   2.- Now you have in the directory "/var/tmp/stb/extract" de ipsrepo  # cd /var/tmp/stb/extract rwxr-xr-x   3 102      staff          4 Mar  8 11:28 ipsrepo -rw-r--r-- 
Post a Comment
More Information »

How to see all spfile parameters

By
SET LINESIZE 300 COLUMN name  FORMAT A30 COLUMN value FORMAT A60 COLUMN displayvalue FORMAT A60 SELECT sp.sid,        sp.name,        sp.value,        sp.display_value FROM   v$spparameter sp ORDER BY sp.name, sp.sid;
Post a Comment
More Information »

How to Reset the Root Password of RHEL-7 / systemd

By
1) Boot your system and wait until the GRUB2 menu appears. 2) In the boot loader menu, highlight any entry and press e . 3) Find the line beginning with linux. At the end of this line, append the following: init=/bin/sh Or if you face a panic, instead of "ro" change to "rw" to sysroot as example below: rw init=/sysroot/bin/sh 4) Press F10 or Ctrl+X to boot the system using the options you just edited. Once the system boots, you will be presented with a shell prompt without having to enter any user name or password: sh-4.2# 5) Load the installed SELinux policy: sh-4.2# /usr/sbin/load_policy -i 6) Execute the following command to remount your root partition: sh4.2# mount -o remount,rw / 7) Reset the root password: Raw sh4.2# passwd root 9) Reboot the system. From now on, you will be able to log in as the root user using the new password set up during this procedure.  
Post a Comment
More Information »