imagetest

发表于 分类于 ， ，

1.获取序列号

1
2
3
4
5
6
7
8
9
10
11
12
13
select 'create sequence ' || SEQUENCE_NAME || ' minvalue ' || MIN_VALUE ||


       ' maxvalue ' || MAX_VALUE || ' start with ' || LAST_NUMBER ||


       ' increment by ' || INCREMENT_BY || ' nocache ' || ' ;'


from DBA_SEQUENCES


where SEQUENCE_OWNER = UPPER('BN_SWP');

2.Oracle进程占用 杀进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT    A.OWNER,        
                     A.OBJECT_NAME,        
                     B.SESSION_ID,        
                     B.ORACLE_USERNAME,        
                     B.OS_USER_NAME,        
                     B.PROCESS,        
                     B.LOCKED_MODE,          
                     C.SID,        
                     C.SERIAL#,        
                     C.PROGRAM      
           FROM    ALL_OBJECTS    A,        
                     V$LOCKED_OBJECT    B,        
                     SYS.GV_$SESSION    C    
         WHERE    (    A.OBJECT_ID    =    B.OBJECT_ID    )    
             AND    (B.PROCESS    =    C.PROCESS    )  
             AND    A.OBJECT_NAME='SWP_PATIENT_EXPINFO_TEST';
             
               alter    system    kill    session    '190,62440'    immediate;

3.Oracle Exp 导出提示表不存在

Oracle11g版本以后系统新增一个参数【deferred_segment_creation】，该参数控制是否不分配segment给新建的表或者导入的空表，默认是true。
  • 修改参数set deferred_segment_creation=false，语句如下：
    alter system set deferred_segment_creation=false
    注意：修改该参数并不能给以前已经导入的空表分配segment
  • 如果想导出就要手动给这些表分配segment，语句如下：
    alter table 表名 allocate extent;
  • 如果空表很多也是有解决办法的，执行下面的语句：
    select 'alter table '|| table_name || ' allocate extent;' from user_tables where segment_created='NO';
  • 把查询结果复制出来执行以下就会给所有的空表分配segment,如下：
    alter table SWP_VITAL_SIGNS_DATA allocate extent;
    alter table SWP_MES_INFO_HISTORY allocate extent;
    alter table SWP_VITAL_SIGNS_BASIC allocate extent;
    alter table BED_CARD_DEVICE_INFO allocate extent;
    alter table DEVICE_ONLINE_INFO allocate extent;
    alter table SWP_LOG allocate extent;

4.ORACLE创建数据库实例脚本

1
2
3
4
5
select file_Name from dba_data_files;
create tablespace BN_SWP datafile '/home/oracle/app/oracle/oradata/orcl/BN_SWP.DBF' size 500m autoextend on next 5M maxsize unlimited
create user BN_SWP  identified by 123456;
alter user BN_SWP  default tablespace BN_SWP;
grant dba  to  BN_SWP;

5.查看oracle服务状态

lsnrctl status

6.查看SID

select instance_name from v$instance;

6.查看服务名

show parameter service_name;

8.自增长id添加序列，触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建序列
create sequence SEQ_COMM_HOSPITAL
minvalue 100
maxvalue 9999999
start with 510
increment by 1
cache 20;

--添加触发器
CREATE OR REPLACE TRIGGER SWP_COMM_HOSPITAL_TRIGGER
  before insert
  on COMM_HOSPITAL 
  for each row
begin
    select SEQ_COMM_HOSPITAL.nextval into:new.ID from dual;
end;

–修改oracle追踪日志文件大小alter system set max_dump_file_size=2000

9.修改密码过期时间

1
2
3
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;