http://www.oracle.com/technology/pub/articles/smiley-11gr1-install.html

http://ss64.com/ora/ ( no commericals)

http://psoug.org/reference/tablespaces.html ( lots of commercials)

http://www.adp-gmbh.ch/ora/sql/alter_database.html (not much just google ads)

How to defragment a table - Do it in test first

1. Always take backup of the tables before moving the data

2. Ensure the tablespace in which the object currently exists has sufficient space. It should have at least 2 times the size of the index free

3. alter table <owner>.<table_name> move;

4. Rebuild indexes associated with the table. Check the index status with this sql:

select owner, index_name, status from dba_indexes
where table_owner = upper('&OWNER') and
table_name = upper('&SEGMENT_NAME');

 alter index <owner>.<index_name> rebuild online;

5. Collect statistics for the tables.

For example:
exec fnd_stats.gather_table_stats ('APPLSYS','FND_CONCURRENT_REQUESTS',PERCENT=>99);

http://ss64.com/orad/USER_OBJECTS.html

Set pagesize 0
define OWNER=Kent
define NEWUSER=Frosty
Spool new_grants.txt

Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;

Spool Off

lsnrctl start/stop/status TEST

PATH=$PATH:$ORACLE_HOME/OPatch
export PATH

$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

$ORACLE_HOME/OPatch/opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc

select b.tablespace_name
       ,tbs_size SizeMb
       ,a.free_space FreeMb
  from 
    (select tablespace_name
            ,round(sum(bytes)/1024/1024 as free_space 
    from dba_free_space 
    group by tablespace_name) a, 
    (select tablespace_name
            , sum(bytes)/1024/1024 as tbs_size 
    from dba_data_files 
    group by tablespace_name
    UNION
    select tablespace_name
           ,sum(bytes)/1024/1024 tbs_size
    from dba_temp_files
    group by tablespace_name ) b
    where a.tablespace_name(+)=b.tablespace_name;

create user xxgl
identified by password
default tablespace APPS_TS_TOOLS
temporary tablespace temp;

grant connect to xxgl

COLUMN value FORMAT 999,999,999,990
COLUMN name FORMAT a31

SELECT a.name,b.value
FROM v$statname a,v$sysstat b
WHERE a.statistic# = b.statistic#
AND a.name like '%redo%';

alter database add logfile group 3
('/u09/oracle/frienddata/log3a.dbf',
 '/u11/oracle/frienddata/log3b.dbf'
)
size 40M;

SELECT FILE_ID,AUTOEXTENSIBLE 
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME='&UNDOTBS';

To make those datafile auto extensible, run the following command.

ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;

If you wish to switch off auto extend and to reuse the UNEXPIRED 
space, do the following


ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND OFF;

(a) If good number of extents in UNEXPIRED status, it could be due to high undo_retention.

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)   
  FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

ALTER DATABASE DATAFILE 39 RESIZE 3000M;

Startup mount

alter database archivelog;

alter database open

Alter system archive log start

Startup mount

alter database noarchivelog;

alter database open

Alter system switch log

file Alter system archive log current

Oracle 8.1.X, 9.X.X, 10.X.X or 11.X.X  
-------------------------------------    
*** NEW IN 8i AND ABOVE ***       
A 'relink' script is provided in the $ORACLE_HOME/bin directory.       
% cd $ORACLE_HOME/bin       
% relink      ...this will display all of the command's options.         
usage: relink <parameter>         
accepted values for parameter: all, oracle, network, client,          
client_sharedlib, interMedia, precomp, utilities, oemagent, ldap        
 Note: ldap option is available only from 9i. In 8i, you would have to manually relink    
ldap.      
You can relink most of the executables associated with an Oracle Server Installation    
by running the following command:       % relink all        
This will not relink every single executable Oracle provides(you can    
discern which executables were relinked by checking their timestamp with    
'ls -l' in the $ORACLE_HOME/bin directory).  However, 'relink all' will     
recreate the shared libraries that most executables rely on and thereby    
resolve most issues that require a proper relink. 



$ORACLE_HOME/bin relink all

SQL> oradebug ipc

Information written to trace file. Trace file is written to USER_DUMP_DEST. The shared memory segment id can be found by looking in the trace file for Shmid:

Then look to verify this is running with the following command

% ipcs -b

or

$ORACLE_HOME/bin/sysresv

Remove all the problematic shared memory segments from the memory:

check the $ORACLE_HOME/dbs directory for lk<SID> files and remove them.

Remove all the problematic semaphores from the memory:

ipcrm -m <shared memory id>

ipcrm -s <semaphore id>

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'

Max open cursor

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; 
       
     

% cd ORACLE_HOME/bin 
% ls -l oracle          
% -rwxr-x--x      As the Oracle user, you do the following:           
% chmod 6751 oracle 

-rwsr-s--x 1 oraman dba 210824714 May 31 11:22 oracle




-rws--x--- 1 root dba 45411 May 31 11:21 nmo


chmod 6751 nmo 

changes it to:

-rwsr-s--x 1 root dba 45411 May 31 11:21 nmo