back002

alter table user enable row movement;

 

 

scp /home/daisy/full.tar.gz root@172.19.2.75:/home/root

netstat -an | grep XXX，

 

 

useradd -u 2017 -d /home/username -g cab -s /usr/bin/csh -m cabtimer

 

 

 

评论

11 楼 leocat 2012-07-16  

SQL> SET ARRAYSIZE 10000
SQL> SET COPYCOMMIT 10000
SQL> SET COPYTYPECHECK OFF
SQL> SET LONG 1000000


COPY FROM username/password@ora11g_old TO username/password@ora11g replace t USING SELECT * FROM t;


COPY FROM username/password@ora11g_old TO username/password@ora11g create t USING SELECT * FROM t  ;

10 楼 leocat 2012-07-16  

exp username/password@ora11g  file=data_bak.dmp tables=table1,table2,table3 buffer=40960000 feedback=10000 statistics=none;

imp username/password@ora11g file=data_bak.dmp fromuser=username touser=username buffer=409600000 commit=y ignore=y feedback=10000;


create directory DPDATA as '/home/oracle/dp'

grant read, write on directory dpdata to username;



expdp username/password@ora11g schemas=username DIRECTORY=DPDATA dumpfile=data.dmp parallel=4

impdp username/password@ora11g schemas=username DIRECTORY=DPDATA dumpfile=data.dmp parallel=4 TABLE_EXISTS_ACTION=REPLACE

9 楼 leocat 2012-07-13  

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class SetOpt
{

    // 交集
    public List intersect(List ls, List ls2)
    {
        List list = new ArrayList(Arrays.asList(new Object[ls.size()]));
        Collections.copy(list, ls);
        list.retainAll(ls2);
        return list;
    }

    // 并集
    public List union(List ls, List ls2)
    {
        List list = new ArrayList(Arrays.asList(new Object[ls.size()]));
        Collections.copy(list, ls);
        list.addAll(ls2);
        return list;
    }

    // 差集
    public List diff(List ls, List ls2)
    {
        List list = new ArrayList(Arrays.asList(new Object[ls.size()]));
        Collections.copy(list, ls);
        list.removeAll(ls2);
        return list;
    }

    public String testIn2(String string)
    {
        StringBuffer strBuff = new StringBuffer(string.length());
        for (int i = string.length() - 1; i >= 0; i--)
        {
            strBuff.append(string.charAt(i));
        }
        return strBuff.toString();
    }

    public static synchronized void getInstance()
    {

    }

    public static void main(String[] args)
    {
        SetOpt opt = new SetOpt();
        List l1 = new ArrayList();
        l1.add(1);
        l1.add(2);
        l1.add(3);
        l1.add(4);
        List l2 = new ArrayList();
        l2.add(3);
        l2.add(4);
        l2.add(5);
        l2.add(6);
        List intersectList = opt.intersect(l1, l2);
        System.out.println("交集：");
        for (int i = 0; i < intersectList.size(); i++)
        {
            System.out.print(intersectList.get(i) + " ");
        }
        System.out.println();
        List unionList = opt.union(l1, l2);
        System.out.println("并集：");
        for (int i = 0; i < unionList.size(); i++)
        {
            System.out.print(unionList.get(i) + " ");
        }
        System.out.println();
        List diffList = opt.diff(l1, l2);
        System.out.println("差集：");
        for (int i = 0; i < diffList.size(); i++)
        {
            System.out.print(diffList.get(i) + " ");
        }
        System.out.println();
    }

}

8 楼 leocat 2012-07-12  

create table ContactNumber as ( 
     select  /*+ PARALLEL(tt,8) */ distinct contact_id,
                     get_primary_mobile(contact_id) as primarymobile,
                     get_alter_mobiles(contact_id) as alternatemobiles,
                     get_format_primary_mobile(contact_id) as frmtprimarymobile,
                     get_format_alter_mobiles(contact_id) as frmtalternatemobiles
                from phones@bmsuite tt
     );

create index IX_CTNUMBER_CONTACT_ID on CONTACTNUMBER (CONTACT_ID)
  tablespace TBS_CABSLAVE_INDEX;

update cab_t_contact_slv ctc set ctc.primarymobile = (select PrimaryMobile from ContactNumber cn where ctc.contactid = cn.contact_id);
     
update cab_t_contact_slv ctc set ctc.alternatemobiles = (select AlternateMobiles from ContactNumber cn where ctc.contactid = cn.contact_id);

update cab_t_contact_slv ctc set ctc.frmtprimarymobile = (select FrmtPrimaryMobile from ContactNumber cn where ctc.contactid = cn.contact_id);
  
update cab_t_contact_slv ctc set ctc.frmtalternatemobiles = (select FrmtAlternateMobiles from ContactNumber cn where ctc.contactid = cn.contact_id);


Insert into cab_t_userdatastatistic_slv (USERID, TOTALCONTACTS, TOTALCONTACTLISTS, TOTALFRIENDS, TOTALAPPLICATIONS, ADDRBOOKVERSION, CONTACTSVERSION, CONTACTLISTSVERSION) select userid,0,0,0,0,to_char(sysdate,'YYYYMMDD')||'-00001',to_char(sysdate,'YYYYMMDD')||'-00001',to_char(sysdate,'YYYYMMDD')||'-00001' from cab_t_user@cabmaster;

update cab_t_userdatastatistic_slv ctu set ctu.totalcontacts = (select count(1) from cab_t_contact_slv cts where cts.userid = ctu.userid);


commit;



drop function get_alter_mobiles;

drop function get_primary_mobile;


drop function get_format_alter_mobiles;

drop function get_format_primary_mobile;


drop table ContactNumber;

commit;

7 楼 leocat 2012-07-12  

create or replace function get_format_alter_mobiles(p_contactid in varchar2) return varchar2 is
  result varchar2(1024);
  v_count integer;
begin
          select count(1) into v_count from phones@bmsuite where is_primary = 0 and contact_id= p_contactid;
               if(v_count >0) then
                  select replace(wm_concat('+' || "NUMBER"), ',', chr(1)) into result
                          from (select "NUMBER"
                                  from (select "NUMBER"
                                          from phones@bmsuite
                                         where contact_id = p_contactid
                                           and is_primary = 0
                                         order by updated_at desc)
                                 where rownum < 6);
                else
                  result := '';
                end if;
  return(Result);
end get_format_alter_mobiles;
/

create or replace function get_format_primary_mobile(p_contactid in varchar2) return varchar2 is
  result varchar2(255);
  v_count integer;
begin
  select count(1) into v_count from phones@bmsuite where is_primary = 1 and contact_id= p_contactid;
                 if (v_count >0) then
                     select '+'||"NUMBER" into result
                            from phones@bmsuite
                           where contact_id = p_contactid
                             and updated_at = (select max(updated_at)
                                                 from phones@bmsuite
                                                where contact_id = p_contactid
                                                  and is_primary = 1)
                                                  and rownum < 2;
                 else
                    result := '';
                 end if;
  return(Result);
end get_format_primary_mobile;
/

6 楼 leocat 2012-07-12  

create or replace procedure init_table
as
  v_count integer;
begin
  select count(*) into v_count from user_tables where table_name = upper('contactnumber');
  if v_count > 0 then
     execute immediate 'drop table contactnumber';
  end if;
 
end;

/
execute init_table();
/

drop procedure init_table;


create or replace function get_alter_mobiles(p_contactid in varchar2) return varchar2 is
  result varchar2(1024);
  v_count integer;
begin
          select count(1) into v_count from phones@bmsuite where is_primary = 0 and contact_id= p_contactid;
               if(v_count >0) then
                  select replace(wm_concat("NUMBER"), ',', chr(1)) into result
                          from (select "NUMBER"
                                  from (select "NUMBER"
                                          from phones@bmsuite
                                         where contact_id = p_contactid
                                           and is_primary = 0
                                         order by updated_at desc)
                                 where rownum < 6);
                else
                  result := '';
                end if;
  return(Result);
end get_alter_mobiles;
/

create or replace function get_primary_mobile(p_contactid in varchar2) return varchar2 is
  result varchar2(255);
  v_count integer;
begin
  select count(1) into v_count from phones@bmsuite where is_primary = 1 and contact_id= p_contactid;
                 if (v_count >0) then
                     select "NUMBER" into result
                            from phones@bmsuite
                           where contact_id = p_contactid
                             and updated_at = (select max(updated_at)
                                                 from phones@bmsuite
                                                where contact_id = p_contactid
                                                  and is_primary = 1)
                                                  and rownum < 2;
                 else
                    result := '';
                 end if;
  return(Result);
end get_primary_mobile;
/

5 楼 leocat 2012-07-12  

alter sequence CAB_S_CONTACTID increment by 149999 nocache;
select CAB_S_CONTACTID.nextval from dual;
alter sequence CAB_S_CONTACTID increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select CAB_S_CONTACTID.currval into LastValue from dual;
    exit when LastValue >= 200000 - 1;
    select CAB_S_CONTACTID.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence CAB_S_CONTACTID increment by 1 cache 50000;


alter sequence CAB_S_CONTACTLISTID increment by 198899 nocache;
select CAB_S_CONTACTLISTID.nextval from dual;
alter sequence CAB_S_CONTACTLISTID increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select CAB_S_CONTACTLISTID.currval into LastValue from dual;
    exit when LastValue >= 200000 - 1;
    select CAB_S_CONTACTLISTID.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence CAB_S_CONTACTLISTID increment by 1 cache 1000;


alter sequence CAB_S_USERID increment by 195999 nocache;
select CAB_S_USERID.nextval from dual;
alter sequence CAB_S_USERID increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select CAB_S_USERID.currval into LastValue from dual;
    exit when LastValue >= 200000 - 1;
    select CAB_S_USERID.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence CAB_S_USERID increment by 1 cache 1000;

4 楼 leocat 2012-07-12  

alter table cab_t_user drop  constraint  PK_USER;
alter table cab_t_user rename to cab_t_user_bak;
create table cab_t_user
(
   USERID             VARCHAR2(32)         not null,
   BMSuiteUserID      VARCHAR2(28)         not null,
   Account            VARCHAR2(128)        not null,
   SipURI             VARCHAR2(128),
   PrimaryMobile      VARCHAR2(21), 
   Status             number(1)            not null,
   RegisterTime       TIMESTAMP            not null,
   LastUpdateTime     TIMESTAMP            not null,
   LastChangeTime     TIMESTAMP,
   OldPrimaryMobile   VARCHAR2(32),
   UnregisterTime     TIMESTAMP,
   DataDeleteDate     DATE,
   ConflictStrategy   number(1)            not null,
   Email              varchar2(64)         null,
   UserType           Integer              not null,
   LastUserDigStartTime TIMESTAMP    default to_timeStamp('2000-01-01 01:00:00.000','YYYY-MM-DD HH:MI:SSXFF PM'),
   HomeLocation       VARCHAR2(5),
   CountryCode        VARCHAR2(5)  
)partition by hash(userid)( 
partition user_data_1 tablespace tbs_cabmaster_data,
partition user_data_2 tablespace tbs_cabmaster_data,
partition user_data_3 tablespace tbs_cabmaster_data,
partition user_data_4 tablespace tbs_cabmaster_data,
partition user_data_5 tablespace tbs_cabmaster_data,

)TABLESPACE tbs_cabmaster_data;

alter table cab_t_user nologging;

insert /*+ APPEND */ into cab_t_user(USERID,BMSuiteUserID,Account,SipURI,PrimaryMobile,Status,RegisterTime,LastUpdateTime,LastChangeTime,OldPrimaryMobile,UnregisterTime,DataDeleteDate,ConflictStrategy,Email,UserType,LastUserDigStartTime)
    select USERID,BMSuiteUserID,Account,SipURI,PrimaryMobile,Status,RegisterTime,LastUpdateTime,LastChangeTime,OldPrimaryMobile,UnregisterTime,DataDeleteDate,ConflictStrategy,Email,UserType,LastUserDigStartTime from cab_t_user_bak;

alter table cab_t_user add constraint PK_USER primary key (USERID) USING INDEX local TABLESPACE tbs_cabmaster_index;

alter table cab_t_user logging;



alter table cab_t_profilephoto drop  constraint  PK_CAB_T_PROFILEPHOTO;

alter table cab_t_profilephoto add partition profilephoto_data_3 tablespace tbs_cabmaster_data;
alter table cab_t_profilephoto add partition profilephoto_data_4 tablespace tbs_cabmaster_data;
alter table cab_t_profilephoto add partition profilephoto_data_5 tablespace tbs_cabmaster_data;


alter table cab_t_profilephoto add    constraint PK_CAB_T_PROFILEPHOTO primary key (USERID) USING INDEX local TABLESPACE tbs_cabmaster_index;




create index IX_PROFILE_PrimaryEMAIL on cab_t_profile (PrimaryEmail ASC) TABLESPACE tbs_cabmaster_index;
create index IX_PROFILEACCOUNT_ACCOUNTNAME on cab_t_profileaccount (AccountName ASC) TABLESPACE tbs_cabmaster_index;
create index IX_PROFILEACCOUNT on cab_t_profileaccount(USERID ASC,SrvType ASC,IsPrimaryFlag ASC) local TABLESPACE tbs_cabmaster_index;
create index IX_PROFILESC_USERID on cab_t_profileschool(USERID ASC) local TABLESPACE  tbs_cabmaster_index;



commit;

3 楼 leocat 2012-07-12  

create or replace procedure init_index
as
  v_count integer;
begin

  select count(*) into v_count from all_ind_columns where table_name = upper('cab_t_user') and index_name = upper('IX_USER_SipURI');
  if v_count > 0 then
     execute immediate 'drop index IX_USER_SipURI';
  end if;
 
  select count(*) into v_count from all_ind_columns where table_name = upper('cab_t_user') and index_name = upper('IX_USER_LastUpdateTime');
  if v_count > 0 then
     execute immediate 'drop index IX_USER_LastUpdateTime';
  end if;
end;

/
execute init_index();
/

drop procedure init_index;



alter table cab_t_profileschool rename to cab_t_profileschool_bak;

create table cab_t_profileschool
(
   USERID             VARCHAR2(32)         not null,
   School             VARCHAR2(64)         not null,
   Major              VARCHAR2(128),
   Batch              VARCHAR2(32),
   LastUpdateTime     TIMESTAMP            not null
)partition by hash(userid)( 
partition profileschool_data_1 tablespace tbs_cabmaster_data,
partition profileschool_data_2 tablespace tbs_cabmaster_data,
partition profileschool_data_3 tablespace tbs_cabmaster_data,

)TABLESPACE tbs_cabmaster_data;

alter table cab_t_profileschool nologging;

insert /*+ APPEND */ into cab_t_profileschool(USERID,School,Major,Batch,LastUpdateTime)
    select USERID,School,Major,Batch,LastUpdateTime from cab_t_profileschool_bak;

alter table cab_t_profileschool logging;

2 楼 leocat 2012-07-12  

#!/bin/bash
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

dbname=$3
username=$1
userpwd=$2
parameterNum=$#
oracleport=1521

echo "$dbname"
echo "$#"

echo "---------------------------------"
echo "database name = : $dbname"
echo "user's name = $username"
echo "user's password = $userpwd"
echo "---------------------------------"

validate()
{
   if [ $parameterNum -ne 3 ]
   then
    echo "parameter error";
    exit 1;
   fi
}

validate
sqlplus $username/$userpwd@$dbname <<EOF
  spool update.cabmaster.log 
 
  @rollbackTable.sql;
  prompt "rollbackTable.sql success..."
 
  spool off
  exit ;
EOF

1 楼 leocat 2012-07-12  

#!/bin/bash
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

dbname=$3
username=$1
userpwd=$2
parameterNum=$#
oracleport=1521

echo "$dbname"
echo "$#"

echo "---------------------------------"
echo "database name = : $dbname"
echo "user's name = $username"
echo "user's password = $userpwd"
echo "---------------------------------"

validate()
{
   if [ $parameterNum -ne 3 ]
   then
    echo "parameter error";
    exit 1;
   fi
}

validate
sqlplus $username/$userpwd@$dbname <<EOF
  spool update.cabmaster.log
  WHENEVER SQLERROR EXIT 1
 
  @cab_maindb_tables.sql
  prompt "cab_maindb_tables.sql success..."
  spool off
  exit ;
EOF