Show_user_with_top_io

SELECT ses.sid, ses.serial#, ses.osuser, ses.process, sio.physical_reads
   FROM v$session ses, v$sess_io sio
  WHERE ses.sid  = sio.sid
    AND nvl(ses.username,'SYS') not in ('SYS', 'SYSTEM')
    AND sio.physical_reads = (SELECT MAX(physical_reads)
                                FROM v$session ses2, v$sess_io sio2
                               WHERE ses2.sid = sio2.sid
                                 AND ses2.username
                                  NOT IN ('SYSTEM', 'SYS'));

Show_statistics_of_connected_users

col osuser format a10 trunc heading "OSUSER AS"
col orauser format a10 trunc
col machine format a10 trunc
col sprogram format a15 trunc
col process format a20 trunc
col server format a3 trunc
col sess_id format 9999
col proc_id format a7
--
SELECT  s.osuser osuser,
        s.username orauser,
        s.machine machine,
        s.program sprogram,
        p.program process,
        s.sid sess_id,
        p.spid proc_id,
        s.logon_time,
        s.server server
FROM    v$session s,
        v$process p
WHERE   s.paddr = p.addr
AND     type != 'BACKGROUND'
AND     p.username is not null
ORDER BY 6
/
col osuser clear
col machine clear
col orauser clear
ttitle off

Rac_balance_delta

col instance_name format a20 heading "Instance|name"
col elapsed_seconds format 999,999 Heading "Elapsed|seconds"
col cpu_ms_ps format 999,999.99 heading "CPU ms|p.s."
col db_ms_ps format 999,999.99 heading "DB time|ms p.s."
set pages 1000
set lines 80
set echo on

WITH cluster_delta as (
    SELECT instance_name, start_timestamp,end_timestamp,
     round((end_timestamp-start_Timestamp)*24*3600,2) elapsed_seconds,
           SUM(CASE WHEN stat_name = 'DB CPU'
                    THEN VALUE/1000 END) cpu_ms,
            SUM(CASE WHEN stat_name = 'DB time'
                     THEN VALUE/1000 END) db_ms
    FROM table(opsg_pkg.service_stat_report())
    JOIN gv$instance using (inst_id)
   GROUP BY instance_name,start_timestamp,end_timestamp
)
SELECT instance_name, elapsed_seconds,
       ROUND(cpu_ms / elapsed_seconds, 2) cpu_ms_ps,
       ROUND(cpu_ms / elapsed_seconds, 2) db_ms_ps
FROM cluster_delta
ORDER BY instance_name; 

Logged on Users

SET linesize 200 PAGESIZE 1000 FEED OFF;
COLUMN "sid,serial#" FORMAT A12
COLUMN "pid" FORMAT 999
COLUMN "status" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A16
COLUMN "prg" FORMAT A30
--
SELECT
       ''''
    || s.sid
    || ','
    || s.serial#
    || ''';'                        "sid,serial#"
     , p.spid                        "pid"
     , s.status                      "status"
     , s.schemaname                  "schema"
     , s.osuser                      "osuser"
     , s.machine                     "box"
     , s.program                     "prg"
     , TO_CHAR(s.logon_time, 'DD.MM.YYYY HH24:MI') "logon_time"
  FROM
       v$session s,
       v$process p
WHERE s.paddr  = p.addr
ORDER BY s.username
/

Invalieds

spool show_summary_invalid_objects.lst
set pause off
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;

ttitle left 'Summary of invalid objects for User: ' sql.user -
skip 2

column object_type format a25 wrap heading 'Object|Type'
column status format a8 heading 'Status'

SELECT DISTINCT (object_type), status, COUNT(*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY owner, object_type, status;

Current Database Account

set termout off
set head off
set termout on

select 'User: '|| user || ' on database ' || global_name,
       '(Terminal='||USERENV('TERMINAL')||
       ', Session-Id='||USERENV('SESSIONID')||')'
from   global_name;

set head on feed on

Asadmin with Remote GlassFish

Asadmin with Remote GlassFish

 I've  faced issue while  working  with Amazon EC2, I'm starting to appreciate how useful theGlassFish asadmin tool is with a remote system. Here are some steps to get GlassFish set up for secure, remote access.

Step 1: Setup

After starting up a remote machine, copy the GlassFish zip file there and unzip it. At this point, I only have the standard ssh and http ports open in my firewall ("security group" on Amazon EC2). If you start with port 4848 open, then someone could access the server through the admin console in a browser before you've had a chance to change the password or admin username.

Step 2: Secure admin

With GlassFish installed, start the server, then change the admin user's password and enable secure administration. Without secure administration on, remote systems cannot talk to the server at all. Here are the commands to run locally from your ssh session:

·                     asadmin start-domain

·                     asadmin change-admin-password

After changing the admin password, you may want to run asadmin login as well to avoid having to specify the password again and again. This will only affect local access to the server (e.g. your environment while ssh'ed into the remote machine). Next:

·                     asadmin enable-secure-admin

·                     asadmin restart-domain

For more information on secure admin, see Tim Quinn's blog on the subject.

Step 3: Enable access

You can now open port 4848 in the security group/firewall so that your local asadmin client can talk to the remote server. If you want to remove the 'admin' user and create an administrative user with a different name, you can now reach the admin console at port 4848 in a browser:

1.             Log in as 'admin' user.

2.             In the left-hand panel, open Configurations, server-config, Security, Realms, admin-realm.

3.             Click Manage Users on the right and create the new user in the asadmin group.

4.             Log out as 'admin' then log in as your new user to delete the 'admin' user.

If you change the admin user, use the new user name in place of 'admin' below.

Step 4: Set up local access

With secure administration turned on, you can now access the remote machine like this (note that the port argument is only needed if you have changed the admin port):

ip-sec> asadmin --host <hostname> --user admin --port 4848 --secure list-applications
Enter admin password for user "admin">
Nothing to list.
Command list-applications executed successfully.

Of course, you may not want to specify the command line options over and over and supply the password manually each time. You can specify these environment variables instead:

·                     AS_ADMIN_HOST

·                     AS_ADMIN_PORT

·                     AS_ADMIN_SECURE (set to "true" without the quotes)

·                     AS_ADMIN_USER

·                     AS_ADMIN_PASSWORDFILE

The last one should be set to the path of a file with these contents:

AS_ADMIN_PASSWORD=<your password>

Now you can access your remote server with simple asadmin commands with no other parameters:

ip-sec}> asadmin list-applications
Nothing to list.
Command list-applications executed successfully.

Note: if you want to see all the parameters that are being used in the asadmin command, you can use the --echo option to have them printed: