【Oracle】Oracle初始化数据库表空间、用户、表（索引、分区）等

[oracle@bogon orcl]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 8 18:54:09 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TEMPORARY TABLESPACE anos_temp
2 TEMPFILE '/home/oracle/app/oradata/orcl/anos_temp01.dbf'
3 SIZE 32M
4 AUTOEXTEND ON
5 NEXT 32M MAXSIZE 2048M
6 EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> CREATE TABLESPACE anos_data
2 LOGGING
3 DATAFILE '/home/oracle/app/oradata/orcl/anos_data01.dbf' 
4 SIZE 32M 
5 AUTOEXTEND ON 
6 NEXT 32M MAXSIZE 2048M
7 EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER USER anoscfg
2 DEFAULT TABLESPACE anos_data
3 TEMPORARY TABLESPACE anos_temp;

User altered.

 

create user anoscfg identified by anoscfg; 
 
grant connect, resource,dba to anoscfg;

 

-- Create table
create table PAPU_1440
(
  TIME_START             NUMBER,
  INTERFACE              NUMBER,
  SGSN_SIGNAL_IP         NUMBER,
  ATTACH_NETWORK_SUCCESS NUMBER,
  ATTACH_NETWORK_FAILED  NUMBER,
  ACTIVATE_PDP_SUCCESS   NUMBER,
  ACTIVATE_PDP_FAILED    NUMBER,
  DEACTIVATE_PDP_SUCCESS NUMBER,
  DEACTIVATE_PDP_FAILED  NUMBER,
  MOD_PDP_SUCCESS        NUMBER,
  MOD_PDP_FAILED         NUMBER,
  RAU_SUCCESS            NUMBER,
  RAU_FAILED             NUMBER,
  IDENTITY_SUCCESS       NUMBER,
  IDENTITY_FAILED        NUMBER
)
partition by range (TIME_START)
(
  partition TBL_EVENT_1406995200 values less than (1408723200)
    tablespace ANOS_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 160K
      next 1M
      minextents 1
      maxextents unlimited
    )
);
-- Create/Recreate indexes 
create index PAPU_1440_TIME_START on PAPU_1440 (TIME_START)
  tablespace ANOS_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152));

load data
append into table NATION
fields terminated by '|' 
TRAILING NULLCOLS
(
  N_NATIONKEY   ,
  N_NAME    ,
  N_REGIONKEY       ,
  N_COMMENT
)

9|INDONESIA|2|counts boost about the quickly ironic instructions. slyly final ideas hang carefully pending packag|
10|IRAN|4|old pinto beans integrate furiously slyly even requests: slyly bold accounts sleep blithely unusual pinto beans. ca|
11|IRAQ|4|nst the deposits. final, regular requests integrate carefully. carefully final acc|
12|JAPAN|2| even foxes use furiously above the carefully express accounts. even requests along the furio|
13|JORDAN|4|fluffily even packages. furiously express accounts nag foxes. thinly final platelets sublate quickly around the fu|
14|KENYA|0|boost furiously regular requests. regular deposits sleep careful|
15|MOROCCO|0| detect evenly among the blithely ironic ideas.|
16|MOZAMBIQUE|0|e requests. packages are blithely dogged platelets; carefully eve|
17|PERU|1|ding ideas sleep carefully across the pe|
18|CHINA|2|lithely special dolphins sleep carefully quickly|
19|ROMANIA|3|he sometimes final decoys cajole against the daring warhorses. dugouts around the fluffily |
20|SAUDI ARABIA|4| express accounts integrate doggedly about the requests. slyly e|
21|VIETNAM|2|nic packages after the pinto beans thrash according to the final depo|
22|RUSSIA|3|fter the regular deposits promise carefully about the fluffily ironic gifts. slyly regular accounts maintain qui|
23|UNITED KINGDOM|3|aggle about the blithely unusual braids. regular foxes sleep.|
24|UNITED STATES|1|pecial deposits boost furiously along the ironic foxes. blithely ironic packages sleep |

sqlldr anoscfg/anoscfg@tmall control=nation.ctl data=nation.tbl readsize=512000000 bindsize=512000000 rows=10000 parallel=y errors=100000 silent=header,feedback,errors,discards,partitions 

exp anoscfg/anoscfg@tmall file=nation.dmp tables=nation

imp anoscfg/anoscfg@orcl file=nation.dmp tables=nation 

 

CREATE TEMPORARY TABLESPACE epcm_d_temp
TEMPFILE 'C:\oraclexe\app\oracle\oradata\XE\epcm_d_temp_01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;



CREATE TABLESPACE epcm_d_data
LOGGING
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\epcm_d_data_01.dbf' 
SIZE 32M 
AUTOEXTEND ON 
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

create user epcm identified by epcm;

 
grant connect, resource,dba to epcm;

alter USER epcm
DEFAULT TABLESPACE epcm_d_data
TEMPORARY TABLESPACE epcm_d_temp;