Oracle notes

Main Site : CNK's space : Unix : Oracle Notes

How to connect with SQLPlus if you don't have a TNSNames file

$ sqlplus user/password@//<IP_ADDRESS>:<PORT>/<SID>

How to delete redundant rows

Cribbed from http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg%5fid=000dwM

2 suggestions:

     delete from companies
     where company_id not in (
        select min(company_id)
        from companies
        group by company_name)
and
     delete from companies c1
     where exists
      (select 1 from companies c2
       where c2.company_name = c1.company_name
         and c2.company_id < c1.company_id)

How to update a table based on information in a second table

Well sometimes you can just update a view that contains all the information you want. But a lot of times that fails. The rule is that primary keys must be preserved during the transaction - but it seems to me like Oracle refuses to do it even when I don't think this rule is violated.

So, one needs to do an update with a sub-select - but the sub-select needs to have a where condition that contains the updated table, as well as the updating table. It also helps clarity (and in cases where you might have trouble with nulls, eliminates problems) if you use a WHERE EXISTS clause to limit the rows available for updating to just those you want.

update calendar
set event_url = 'http://events.caltech.edu/events/event-' || (select ope_event_id
							      from ope_events E1
							      where calendar.fmp_id = E1.fmp_id) || '.html'
where exists (select calendar_id
              from calendar C1
              where fmp_id is not null
              and event_url is null
              and C1.calendar_id = calendar.calendar_id)
Cribbed from p 156 of Joe Celko's SQL for Smarties

How to add a constraint

I always forget how to add a named constraint to an existing table:
     alter table fsp_info add constraint fsp_info_snp_id_fk 
        foreign key (snp_id) references snp_info(group_id);
OR
     alter table ec_orders add (
         constraint privacy_level_check check (privacy_level in ('all', 'name', 'none')));
OR
     alter table ope_series add constraint sort_order_uq unique (sort_order);

How to figure out what constraints depend on data in a specific table

SQL> select owner, constraint_name, table_name  
from user_constraints  
where r_constraint_name in (select constraint_name  
                            from user_constraints  
                            where table_name = 'CALENDAR_CATEGORIES'); 

How to turn off variable prompting in a SQL*Plus session

If you have a litteral that contains an ampersand (&), SQL*Plus thinks this is a signal to ask you for the value of a variable. Extremely annoying. To turn this feature off for the duration of your session:
SQL> set define off

How to double check what database your SQL*Plus session is logged into

Figuring out your user is easy:
show user
But figuring out the database is a smidge harder:
show parameter service_names

How to set a bind variable in SQL*Plus session

SQL> variable user_id number 
SQL> begin 
  2  :user_id := 21; 
  3  end;

PL/SQL procedure successfully completed. 
 
SQL> print user_id 
 
   USER_ID 
---------- 
        21 

SQL> select user_state from users where user_id = :user_id; 
 
USER_STATE 
--------------------------------------------------------------------------------
                                                                                 
authorized 

How to kill Oracle sessions

Before doing a clean shutdown, you need to kick all the users off. In our circumstances, that usually means just shutting down the web servers. But if there are sessions that Oracle thinks are active after that - especially ones you can't find in the process table - you may need to kill them with ALTER SYSTEM. First, identify the sessions that need killing:
  SQL> select sid, serial#, username, process, status from v$session;
Then kill them like:
  SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

NLS mismatches

To import from US7ASCII to UTF8 you need to unset your NSL_LANG environment variable before doing import. This forces imp to do the character set conversion. Beware of imports where versions of imp and exp do not match. See this "Ask Tom" article about imp-00069 errors.

To move tables to new tablespace

Create the tablespaces that are used in the database you are exporting from. Do the import, letting tables sort themselves into either the new user's default tablespace, or the original one (which they will do if there are *lob columns). The use the following PL/SQL to move them all into the new user's default tablespace and drop the unwanted tablespace.
    set serveroutput on
    begin
    for one in (select table_name from user_tables
    where tablespace_name = 'GIVINGCAPITAL'
    ) loop
    dbms_output.put_line('Moving '||one.table_name);
    execute immediate 'alter table '||one.table_name||' move tablespace makewaves';
    end loop;
    end;
    /


    begin
    for one in (select index_name from user_indexes where status = 'UNUSABLE')
    loop
    dbms_output.put_line('Updating '||one.index_name);
    execute immediate 'alter index '||one.index_name||' rebuild online';
    end loop;
    end;
    /

Other


cnk@ugcs.caltech.edu