Connect to system space as sysdba: (su oracle)
sqlplus “system/manager as sysdba”

List oracle running processes:
select pid as ora_pid, spid as nix_pid, serial#, background
from v$process

See which ora session is associated with which OS process:
select sid as session_id, serial#, status, process as nix_pid, sql_hash_value
from v$session;

See what SQL an oracle session is running:
select pid as ora_pid, spid as nix_spid, s.sid as session_id, users_executing, sql_text
from v$process p, v$session s, v$sqlarea a
where p.addr = s.paddr
and s.sql_hash_value = a.hash_value
order by cast(spid as int)

Declare a bind variable in Sql*Plus:
variable my_var number;

begin
:my_var := 123;
end;
/

print my_var

Have Sql*Plus ask you for a value in a query:
select *
from tblFoo
where foo_id = &myVar

Have Sql*Plus output a query as an html table:
sqlplus -s -m “HTML ON TABLE ‘border=0 cellpadding=2 cellspacing=2′” mydbuser/mydbpass @report_query.sql > report_file.html

Show user grants:
(as sysdba) select * from dba_role_privs;

Show used/free space by tablespace (in MB):

SELECT Total.name “Tablespace Name”,
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name

Shutting down oracle:
shutdown normal

Starting up oracle:

svrmgrl

connect internal

startup

Modifying lob columns:

ALTER TABLE <table name>
    ADD (<lobcol> <LOBTYPE> <LOB_clause_same_as_for_create>) |
    MODIFY LOB (<lobcol>) (
                [PCTVERSION <version_number>]
                [ { CACHE | NO CACHE [{LOGGING | NOLOGGING}]
                          | CACHE READS [{LOGGING | NOLOGGING}]
                  }
                ]
    ) |
    MOVE [ONLINE] [<physical_attributes>] [TABLESPACE <tablespace_name>]
    [LOGGING | NOLOGGING] [<LOB_clause_same_as_for_create>]

This entry was posted on Friday, April 25th, 2008 at 15:09 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.