Getting one of these?

ORA-01555: snapshot too old: rollback segment number 1 with name “blahblahblah” too small 

Fix it like this:

Find how long your longest running query is taking

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)

————————

            6605

Find out what your undo_retention parameter is set to

SQL> sho parameter undo_retention
NAME                                 TYPE        VALUE—————————————————— —————- —————————undo_retention                       integer     1800

SQL> alter system set undo_retention=7000 scope=both;

so you can monitor the current sessions and determine what SQL is being issued, but do you want to determine what the current value of a bind variable is?  some Google searching led me here: http://halisway.blogspot.com/2007/02/viewing-bind-variable-values-in-10g.html

The query:

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='f8pavn1bvsj7t'
/

Thanks for the help!

but just in case you ever need to:

ps -ef|grep ‘oracle<SID> (LOCAL=NO)’|awk ‘{print $2}’ | xargs kill -9

now seriously, you should NEVER have to do this…

for real…this is at your own risk, not my problem if you blow something up.

did i mention that you should NEVER do this?

As sys or system:

SELECT SUM (bytes) / 1024 / 1024 size_in_MB
FROM dba_segments
WHERE (segment_name = ‘TABLE_NAME’
OR segment_name IN (SELECT segment_name
FROM dba_lobs
WHERE table_name = ‘TABLE_NAME’
UNION
SELECT index_name
FROM dba_lobs
WHERE table_name = ‘TABLE_NAME’))

As the table owner:

SELECT SUM (bytes) / 1024 / 1024 size_in_MB
FROM user_segments
WHERE (segment_name = ‘TABLE_NAME’
OR segment_name IN (SELECT segment_name
FROM user_lobs
WHERE table_name = ‘TABLE_NAME’
UNION
SELECT index_name
FROM user_lobs
WHERE table_name = ‘TABLE_NAME’))

Every SQL Server DBA should already know this one, but if you want to flush out the inactive entries in your log file, do this:

USE db_name
backup log dbname with truncate_only
dbcc shrinkfile(dbname_log,1)

For more information, go here: Microsoft

Ever fat finger a datafile name and wish you could change it?  It’s easy:

Identify the tablespace that contains the datafile in question and set it to offline

SQL> alter tablespace ABC offline normal;

Locate the file in question and use OS commands to move/rename it

[oracle@bigserver oradata]$ mv abc01 abc01.dbf

Issue the alter tablespace command to officially rename the file in Oracle

SQL> alter tablespace ABC RENAME DATAFILE ‘/oracle/oradata/abc01’ TO ‘/oracle/oradata/abc01.dbf’;

Bring the tablespace back online

SQL> alter tablespace XXVSX online normal;

TAKE A BACKUP!  Always after a structural change, immediately take a complete backup of the DB.

Here is a script to make quick work of the sometimes annoying request to grant Joe rights to select from all of Mary’s tables.  Don’t be a bonehead and give Joe rights to select everything, do it right.

Logged in as SYSTEM or another privileged user

SELECT ‘GRANT SELECT ON USERA.’||table_name||’ TO USERB;’
FROM dba_tables where owner=’USERA’

Either spool the results to a .sql file and execute, or copy and paste the results into SQL*Plus

Create an RMAN tablespace:

SQL> create tablespace “RMAN” datafile ‘/xxx/rman01.dbf’ size 100M Autoextend on;

Create the RMAN user:

SQL> CREATE USER rman IDENTIFIED BY rman TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;

Grant the RMAN user what he needs:

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Create the recovery catalog:

[bash]$ rman catalog=rman/rman

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Nov 3 12:55:00 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace “RMAN”;

recovery catalog created

RMAN> exit

Recovery Manager complete.

Register Database

[bash]$ rman catalog=rman/rman target=system/password

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Nov 3 12:57:52 2009Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: DB (DBID=2072231354)
connected to recovery catalog database

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog

full resync complete
RMAN>

Found (http://howto.wikia.com/wiki/Oracle_Applications_HOWTO:_modify_LOB_storage)

Error Message:

ORA-1693: max # extents 2000 reached in lobsegment APPLSYS.SYS_LOB0XX$$

Fix:

IDENTIFY THE OFFENDING TABLE:

select owner, table_name, column_name
from dba_lobs
where segment_name = ‘APPLSYS.SYS_LOB0XX$$’;

Assuming:

owner = APPLYSYS
table = WF_NOTIFICATION_OUT
column = “USER_DATA”.”TEXT_LOB”

SET IT TO UNLIMITED EXTENTS

alter table APPLSYS.WF_NOTIFICATION_OUT
modify lob (“USER_DATA”.”TEXT_LOB”) (STORAGE (MAXEXTENTS UNLIMITED));

SQL> set heading off;
SQL> set echo off;
SQL> Set pages 999;
SQL> set serveroutput on;
SQL> set long 99999;
SQL> spool /tmp/indexes.sql
SQL> select DBMS_METADATA.get_ddl(‘INDEX’,INDEX_NAME, OWNER) from dba_indexes WHERE table_name in (‘YOUR TABLES’);

CREATE UNIQUE INDEX “LARRY”.”LARRY_IDX” ON “LARRY”.”LARRYS_TABLE” (“ACCOUNT_NUMBER”)
PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAX
EXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) TABLESPACE “LARRYS_TBSP”

…etc…

142 rows selected.

SQL> spool off;