MarkD's knowledge base for Oracle

AOLserver

How do I insert a CLOB via AOLserver?

set insert_sql "
     insert into news_articles (body, headline)
     values (empty_clob(), empty_clob()) 
  returning body, headline into :1, :2
"

ns_ora clob_dml $db $insert_sql $body $headline

How do I update a CLOB via AOLserver?

set update_sql "
   update news_articles 
   set
      body = empty_clob(),
      headline = empty_clob()
   where item_id = $item_id
   returning body, headline into :one, :two"

ns_ora clob_dml $db $update_sql $body $headline

Constraints

Add a primary key to an existing table
alter table bmf add ( primary key (ein) ) ;
Adding a 'refers to' constraint to an existing column
SQL> alter table upa_test add constraint foo foreign key (users) references users;
Adding a primary key after-the-fact
SQL> alter table contest_domains add (primary key(domain_id));
Adding a unique constraint
SQL> alter table contest_domains add (unique(domain));
Adding non-nullness to a column
SQL > alter table contest_extra_columns modify (domain_id not null);
Changing a column to be not null
alter table search modify (upper_org_name not null);
Don't complain while manipulating constraints
If you need to put a table into an inconstant state while manipulating constraints, you can do this:
SQL> set constraints all deferred; SQL> ... SQL> set constraints all immediate;
Removing a not-null constraint
SQL> alter table blah modify (column null);
Turning off a primary key
SQL> alter table contest_domains drop primary key cascade;

DBA

Adding a datafile to a tablespace
SQL> alter tablespace greentravel_dev add datafile '/ora8/m05/oradata/ora8/greentravel_dev04.dbf' size 2500M autoextend off;
Analyze all statistics in a schema
SQL> exec dbms_utility.analyze_schema (schema => 'QUEENDEV', method => 'COMPUTE');
Changing AutoExtend
alter database datafile '/ora8/m03/oradata/ora8/drsys01.dbf' autoextend off;
Changing table storage paramters
SQL> alter table BORK storage (intial 5K next 128K pctincrease 0);
Create User create user markd identified by markdsucks default tablespace MARKD temporary tablespace TEMP quota unlimited on MARKD; grant create session to markd; grant connect to markd; grant resource to markd;
Creating TableSpace create tablespace GUIDESTAR datafile '/ora8/m02/oradata/ora8/guidestar01.dbf' size 4000M autoextend on permanent ;
Dropping tablespace after deleting data files connect internal startup mount alter database datafile '/ora8/m02/oradata/ora8/blah01.dbf' offline drop; alter database open drop tablespace blah including contents
Fixing ora-03232 SQ>> alter database datafile '/ora8/m02/oradata/ora8/temp01.dbf' autoextend on next 128K maxsize unlimited;
Granting create permission to a tablespace SQL> alter user proteomedev quota unlimited on proteome_indx;
Importing a dump file that's been created by a DBA account If you create an oracle dump file from an account which has DBA privileges, you won't be able to import that dump file into another database. To fix, revoke dba privileges from the user you're using to export as
Installing plustrace / autotrace % cd $ORACLE_HOME/sqlplus/admin source plustrce.sql % cd $ORACLE_HOME/rdbms/admin source utlxplan.sql
Killing orphaned oracle process Run Jin's oradmin/users.sql script to find the oracle session number and serial number. SQL> alter system kill session 'sessionno,serialno';
Moving a table between tablespaces and if you're unfortunate enough to have LOBs SQL> alter table schema.table move lob(column) store as (tablespace destination);
Moving a table partition between tablespaces and if you're unfortunate enough to have LOB partitions SQL> ALTER TABLE schema.table MOVE PARTITION partition LOB (column) STORE AS (TABLESPACE destination)
Removing jobs from the oracle job queue. Do this as the schema user in question SQL> execute dbms_job.remove(3);
Resizing Datafiles alter database datafile '/ora8/m03/oradata/ora8/drsys01.dbf' resize 100M;
Revoking DBA privileges To revoke DBA privileges from a user: alter user markd revoke DBA
Revoking DBA priviliges SQL> revoke dba from gsdev;
Seeing jobs in the oracle job queue SQL> select select job, schema_user, what from dba_jobs;
Seeing patches applied to oracle Use the 'owhat' command. % owhat /ora8/m01/app/oracle/product/8.1.6/bin/oracle

 recovering from deleted datafile
In svrmgrl:       STARTUP MOUNT
       alter database datafile 'yourdatafilename.dbf' offline drop;
       alter database open;

resizing a datafile SQL> alter database datafile '/ora8/m02/oradata/ora8/admirror01.dbf' resize 25M
seeing the privileges a user has SQL> select grantee, granted_role from dba_role_privs where grantee='GRANDEX';


Data Dictonary

 Calculating Buffer Cache Hit Ratio
select (1 - (physical.value / (block_gets.value + consistent.value))) * 100
         "Buffer Cache hit ratio"
  from v$sysstat block_gets, v$sysstat consistent, v$sysstat physical
 where block_gets.name = 'db block gets'
       and consistent.name = 'consistent gets'
       and physical.name = 'physical reads';

 Determinig the Oracle character set
SQL>  select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET;
 Finding and breaking deadlocks
SQL> select sid,type,lmode,request,block from
 v$lock where block != 0 or request != 0 order by 
 lmode, block, sid desc;

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
        69 TM          0          3          0
        67 TM          0          3          0
        60 TM          0          3          0
        57 TM          5          0          1

SQL> select sql_text from v$sqltext_with_newlines 
where hash_value in (select sql_hash_value from 
v$session where sid in (69,67,60,57));

SQL> select sid, serial# from v$session where sid 
in (69,67,60,57);

       SID    SERIAL#
---------- ----------
        57        160
        60        125
        67         41
        69         39

SQL> alter system kill session '57,160';

etc.

 Finding number of extents an index is consuming
SQL> select count(*) from dba_segments where segment_name='YOUR_INDEX_NAME';

 Finding the table that owns a SYS_LOBXXX column
If you see a big segment that starts with SYS_LOB, you can find the owning table with:
SQL> select owner||'.'||table_name "TABLE", column_name, segment_name from all_lobs where segment_name='SYS_LOB0000036083C00002$$';

 Getting a constraint's table and columns

SQL> select table_name from user_constraints where constraint_name='SYS_3193921';

SQL> select column_name from user_cons_columns where constraint_name='SYS_3193921';

 Reconstructing PL/SQL
SQL> select text from user_source where name = 'PROC_NAME' order by line;
 See Available Views
select view_name from user_views
 See all available indices
select index_name, uniqueness from user_indexes
 See the definition for a view
set long 20000

select text from user_Views where view_name ='BASIC_NPO'
 Seeing all of the indices on a table

SQL> set linesize 150

SQL> column column_name format a20

SQL> select i.index_name, i.table_name, i.index_type, c.column_name from ind i, user_ind_columns c where i.table_name='TABLE_NAME' and i.index_name = c.index_name;
 Seeing what primary keys are there
select column_name,position from all_cons_columns where constraint_name in (select constraint_name from all_constraints where table_name='BASE_GIF' and constraint_type='P')
 figuring out the indices on a table
select ui.index_name, ui.index_type, uic.column_name,  uic.column_position as posn, uic.descend  from user_indexes ui, user_ind_columns uic where ui.table_name='GT_PRODUCTS' and ui.index_name=uic.index_name;
 finding size of tables (and other stuff)
SQL> select segment_name, sum(bytes) / (1024 * 1024) sumola from dba_segments where owner='AWAY' group by segment_name order by sumola;
 seeing what tables live in a given tablespace
SQL> select table_name from user_tables where tablespace_name='GREENTRAVEL';

General

 Access Control
SQL> grant select on bboard to greentravel;
 Add a column to an existing table
alter table glassroom_modules add (module_name varchar(100));
 Adding a column to an existing table
alter table search add (upper_org_name varchar(100));
 Changing an existing column
SQL> alter table affiliates modify (affiliate_key varchar(40));
 Create an index
create index bmf_by_ein on bmf (ein);
 Creating a bitmapped index
SQL> create bitmap index index_name on table(column);
 Creating a reverse index
SQL> create index users_email_rev_idx on users(email) reverse;
 Creating create table statements from an exp file
% imp arfdigita/arfdigitarules file=markd.dmp indexfile=stdout > oopack
 Disabling a trigger
SQL^gt; alter trigger gt_product_ctx_del_trigger disable;
 Doing a substr on a CLOB
dbms_lob.substr(column,length,startpos)


Even works in a select!
 Easy query optimization
analyze table table_name estimate statistics

(actually works sometime!)
 Exporting to multiple files
 % exp markd/markdsucks consistent=y filesize=1G file=expdat.dmp file=expdat2.dmp file=expdat3.dmp
 Getting a query plan if you can't use autotrace
Make sure your schema has loaded $ORACLE_HOME/rdbms/utlxplan.sql

SQL> truncate table plan_table;

SQL> explain plan for select count(*) from acs_objects;
SQL> select id, parent_id,
        lpad ('    ', 2*level) || operation  access_plan
        options, object_name
   from plan_table
connect by prior_id = parent_id
  start with id = 0;

 How can I fix oracle errors?
Say your machine is melting down and sqlplus gives you an "ORA-00020" error message.
Try typing "oerr ORA 00020" and see what it tells you.
 Optimization/Profiling
* Feed SQLPlus $ORACLE_HOME/rdbms/admin/utlxplan.sql

* Feed SQLPlus $ORACLE_HOME/sqlplus/admin

* Grant plustrace to the user in question

* set autotrace on

* set time on

* have fun
 Optimization/Profiling II
If you don't want to see the results of the sql query, do

SQL> set autotrace traceonly explain statistics 
 Renaming a table
SQL> rename old-name to new-name;
 Where does tkprof put its trace files?
SQL> select value from v$parameter where name='user_dump_dest'
 making a duplicate of a table
SQL> create table markd_blah as select * from blah
 restarting the TNS listener
% lsnrctl
LSNRCTL> start
 setting UTF8 NLS_LANG
% setenv NLS_LANG .UTF8
 updating part of a table from another one
SQL> update static_pages sp set page_title = (select page_title from static_pages_2 where page_id = sp.page_id);
 using tkprof
SQL> alter session set sql_trace true; 

... run the query ...

% tkprof trace_file output_file explain=meathead/meatheadsucks sort='(prsela,exeela,fchela)' 

 where is the oracle alert log
look in the /ora8/m01/app/oracle/admin/ora8/bdump directory.

Intermedia

 Creating basic text index
create index newsindex on mytable(news) indextype is ctxsys.context;
 Granting intermedia permissions
SQL> grant ctxapp to markd;

Oracle

 Difference between 8i Enterprise and Standard editions
http://technet.oracle.com/doc/oracle8i_816/server.816/a76962/ch4.htm

SQL*Plus

 Changing concept of line length
SQL> set linesize 150
 Seeing BLOB data in sqlplus
To see the first 2000 characters of a blob:

SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(content,2000,1)) from acs_contents
 Source a file in SQL*Plus
Use the @ sign:

SQL> @ /ora8/m01/app/oracle/product/8.0.5/rdbms/admin/utlxplan.sql
 set width of a column
SQL> column constraint_name format a12










admin@badgertronics.com