oracle 创建数据库 表空间 用户等

oracle 创建数据库 表空间 用户等

1.创建DBLink

-- Create database link 
create public database link DFZQWDZX_LINK
  connect to tgread IDENTIFIED BY "a123456"
 using '(DESCRIPTION= (ADDRESS_LIST=
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   ) (CONNECT_DATA=(SERVICE_NAME=wdzx) ) )';

2.创建同义词

create or replace synonym A for TEST.A@DB_TEXT_LINK;

3.创建表空间(需要使用sys进行登录)

CREATE TABLESPACE  TBS_OTHER  NOLOGGING DATAFILE  'TBS_OTHER.ORA' SIZE 10M  REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE  TBS_OTHER_IDX  NOLOGGING DATAFILE  'TBS_OTHER_IDX.ORA' SIZE 10M  REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;

4.表空间赋权

alter user TEST quota unlimited on TBS_OTHER;
alter user TEST quota unlimited on TBS_OTHER_IDX;

5.创建Types

create or replace type TEST_RECORD force IS  object(
    code         VARCHAR2(15) NULL, 
    name         VARCHAR2(32) NULL, 
    id       NUMBER(19,4) NULL, 
    titile        VARCHAR2(20) NULL
);
create or replace type TEST_TYPE IS TABLE OF TEST_RECORD;

CREATE OR REPLACE TYPE num_arr IS TABLE OF number;

6.创建物化视图

-- 删除物化视图
drop materialized view pr_vw_par_hang_ye ;
-- 创建物化视图
create  materialized view pr_vw_par_hang_ye 
tablespace tbs_perform
build immediate
refresh force 
on demand
start WITH SYSDATE next TRUNC(SYSDATE,'dd')+1+20/24
as
select nvl(INDUSTRIESALIAS, 'M') HANG_YE,'ZJH' FEN_LEI, B.S_INFO_WINDCODE SCDM
          from ASHAREINDUSTRIESCODE A,(select max(SEC_IND_CODE) SEC_IND_CODE,S_INFO_WINDCODE
          from (select substr(SEC_IND_CODE, 0, 4) SEC_IND_CODE,
                       substr(S_INFO_WINDCODE,
                              1,
                              instr(S_INFO_WINDCODE, '.') - 1) S_INFO_WINDCODE
                  from A
                 where CUR_SIGN = '1') group by S_INFO_WINDCODE) B
         where used = 1
           and LEVELNUM = '2' and  INDUSTRIESCODE like B.SEC_IND_CODE||'%';
-- 创建物化视图索引
CREATE INDEX INDEX_VW_HANG_YE ON pr_vw_par_hang_ye(SCDM) tablespace tbs_perform_idx;

7.重新启动数据库

export ORACLE_SID=perform
sqlplus /nolog
connect / as sysdba
startup force;
exit;

8.修改数据库进程数

alter system set processes=3000 scope=spfile;
alter system set sessions=3350 scope=spfile;

9.创建数据库用户

  -- Create the user 
create user PERFORM IDENTIFIED BY PERFORM
  default tablespace TBS_PERFORM
  temporary tablespace TEMP
  profile DEFAULT
  password expire
  quota unlimited on tbs_perform
  quota unlimited on tbs_perform_idx
  quota unlimited on TBS_PERFORM_HIST;

10.创建自动分区表

-- Create table
create table SYS_OPERATE_LOG
(
  operate_log_id NUMBER(16) not null,
  ip_address     VARCHAR2(64),
  mac_address    VARCHAR2(32),
  user_info      VARCHAR2(32),
  menu_code      VARCHAR2(8),
  operation      VARCHAR2(64),
  is_success     VARCHAR2(16),
  operate_time   VARCHAR2(20),
  range_time     DATE
)
partition by range(RANGE_TIME)
  interval(numtoyminterval(1,'year'))(
              partition SYS_OPERATE_LOG_T01 values less than(to_date('2005-01-01','yyyy-mm-dd'))
  )tablespace TBS_PERFORM_HIST
	pctfree 10
	initrans 1
	maxtrans 255
	storage
	(
	  initial 80K
	  next 1M
	  minextents 1
	  maxextents unlimited
	  pctincrease 0
	)
;

11.数据库监听启动与关闭

# 启动监听
lsnrctl start
# 关闭监听
lsnrctl stop