oracle建库过程

oracle数据库建立步骤

1、建表空间：

create tablespace icmp datafile '/u01/oradata/wilson/icmp.dbf' size 300M autoextend on next 100M maxsize 2048M extent management local;

建立临时表空间：
 create temporary tablespace linkprobe_fzx2_temp tempfile '/u01/oradata/wilson/linkprobe_fzx2_temp.dbf' size 300M autoextend on next 100M maxsize 2048M extent management local;

查询表空间：
 select tablespace_name from dba_tablespaces;
2、建用户：
create user linkprobe_fzx2 identified by linkprobe_fzx2 default tablespace linkprobe_fzx2 temporary tablespace linkprobe_fzx2_temp;

查询用户名：
 select username from dba_users;
3、授权：
grant create session,create any table,create any view,create any index,create any procedure,alter any table,alter any procedure,drop any table,drop any view,drop any index,drop any procedure,select any table,insert any table,update any table,delete any table to linkprobe_fzx2;
grant dba to linkprobe_fzx2;

4、查看数据库编码
select *from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';
或者
select *from v$nls_parameters where parameter='NLS_CHARACTERSET';

修改数据库编码
export NLS_LANG=AMERICAN_AMERICA.***  （查询的结果）

5 建立表

create table new_point(
ID NUMBER PRIMARY KEY,
Name VARCHAR2(128) NOT NULL,
IN_IP VARCHAR2(128) NOT NULL,
OUT_IP VARCHAR2(128) NOT NULL,
Time DATE NOT NULL,
TYPE NUMBER NOT NULL,
COUNT NUMBER NOT NULL,
COUNT_ALL   NUMBER NOT NULL,
TTL NUMBER NOT NULL);

 

6、建立自增序列

create sequence Seq_New_Point;

select * from user_sequences;

7、建立触发器

create or replace trigger trigger_New_Point
before insert on New_Point
for each row
begin
select Seq_New_Point.nextval into :new.ID from dual;
end trigger_New_Point;
/