创建用户、表空间、分区表

yedehua

浏览: 115358 次
性别:
来自: 东莞

最近访客更多访客>>

wangyy

maoweiwer

qq1716768211

zz123321123

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

mysql:

insert into mysql.user(Host,User,Password) values("localhost","user",password("pwd"));

flush privileges;

grant all on friends.* to user@'%' identified by 'pwd';

flush privileges;

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pwd' WITH GRANT OPTION;

flush privileges;

oracle:

imp mobilemsg/mobilemsg file=E:\2012workspace\smarthome\mobilemsg\mobilemsg.dmp log=E:\2012workspace\smarthome\mobilemsg\mobilemsg.log fromuser=mobilemsg touser=mobilemsg

/*第1步：创建临时表空间 */

create temporary tablespace mobilemsg_temp

tempfile 'E:\oracle\product\10.2.0\oradata\mobilemsg\mobilemsg_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

/*第2步：创建数据表空间 */

create tablespace mobilemsg_data

logging

datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\mobilemsg_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

/*第3步：创建用户并指定表空间 */

create user mobilemsg identified by mobilemsg

default tablespace mobilemsg_data

temporary tablespace mobilemsg_temp;

/*第4步：给用户授予权限 */

grant connect,resource,dba to mobilemsg;

create tablespace sendlogdetail_20000101 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\sendlogdetail_data_2000-01-01.dbf' size 10m autoextend on next 5m maxsize 100m;

CREATE TABLE SEND_LOG_DETAIL

(

ID NUMBER(38,0) NOT NULL,

SEND_PHONE NVARCHAR2(20) NOT NULL,

RECEIVER_PHONE NVARCHAR2(20) NOT NULL,

SEND_TIME TIMESTAMP (6),

RESULT_FLAH CHAR(3 BYTE),

SEND_LOG_ID NUMBER(38,0),

SESSION_ID NVARCHAR2(200),

SAVE_DATE NVARCHAR2(30),

PRIMARY KEY (ID)

)

partition by range (SAVE_DATE)

(

partition partition_2000 values less than (to_date('2000-01-01','YYYY-MM-DD')) tablespace sendlogdetail_20000101

);

create tablespace ts_logdetail_20120808 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-08.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120808 values less than (to_date('2012-08-08','YYYY-MM-DD')) tablespace ts_logdetail_20120808

create tablespace ts_logdetail_20120809 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-09.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120809 values less than (to_date('2012-08-09','YYYY-MM-DD')) tablespace ts_logdetail_20120809

create tablespace ts_logdetail_20120810 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-10.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120810 values less than (to_date('2012-08-10','YYYY-MM-DD')) tablespace ts_logdetail_20120810

create tablespace ts_logdetail_20120811 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-11.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120811 values less than (to_date('2012-08-11','YYYY-MM-DD')) tablespace ts_logdetail_20120811

create tablespace ts_logdetail_20120812 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-12.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120812 values less than (to_date('2012-08-12','YYYY-MM-DD')) tablespace ts_logdetail_20120812

create tablespace ts_logdetail_20120813 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-13.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120813 values less than (to_date('2012-08-13','YYYY-MM-DD')) tablespace ts_logdetail_20120813

create tablespace ts_logdetail_20120814 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-14.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120814 values less than (to_date('2012-08-14','YYYY-MM-DD')) tablespace ts_logdetail_20120814

create tablespace ts_logdetail_20120815 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-15.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120815 values less than (to_date('2012-08-15','YYYY-MM-DD')) tablespace ts_logdetail_20120815

create tablespace ts_logdetail_20120816 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-16.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120816 values less than (to_date('2012-08-16','YYYY-MM-DD')) tablespace ts_logdetail_20120816

create tablespace ts_logdetail_20120817 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-17.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120817 values less than (to_date('2012-08-17','YYYY-MM-DD')) tablespace ts_logdetail_20120817

create tablespace ts_logdetail_20120818 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-18.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120818 values less than (to_date('2012-08-18','YYYY-MM-DD')) tablespace ts_logdetail_20120818

create tablespace ts_logdetail_20120819 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-19.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120819 values less than (to_date('2012-08-19','YYYY-MM-DD')) tablespace ts_logdetail_20120819

create tablespace ts_logdetail_20120820 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-20.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120820 values less than (to_date('2012-08-20','YYYY-MM-DD')) tablespace ts_logdetail_20120820

create tablespace ts_logdetail_20120821 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-21.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120821 values less than (to_date('2012-08-21','YYYY-MM-DD')) tablespace ts_logdetail_20120821

create tablespace ts_logdetail_20120822 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-22.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120822 values less than (to_date('2012-08-22','YYYY-MM-DD')) tablespace ts_logdetail_20120822

create tablespace ts_logdetail_20120823 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-23.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120823 values less than (to_date('2012-08-23','YYYY-MM-DD')) tablespace ts_logdetail_20120823

create tablespace ts_logdetail_20120824 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-24.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120824 values less than (to_date('2012-08-24','YYYY-MM-DD')) tablespace ts_logdetail_20120824

create tablespace ts_logdetail_20120825 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-25.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120825 values less than (to_date('2012-08-25','YYYY-MM-DD')) tablespace ts_logdetail_20120825

create tablespace ts_logdetail_20120826 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-26.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120826 values less than (to_date('2012-08-26','YYYY-MM-DD')) tablespace ts_logdetail_20120826

create tablespace ts_logdetail_20120827 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-27.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120827 values less than (to_date('2012-08-27','YYYY-MM-DD')) tablespace ts_logdetail_20120827

create tablespace ts_logdetail_20120828 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-28.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120828 values less than (to_date('2012-08-28','YYYY-MM-DD')) tablespace ts_logdetail_20120828

create tablespace ts_logdetail_20120829 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-29.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120829 values less than (to_date('2012-08-29','YYYY-MM-DD')) tablespace ts_logdetail_20120829

create tablespace ts_logdetail_20120830 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-30.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120830 values less than (to_date('2012-08-30','YYYY-MM-DD')) tablespace ts_logdetail_20120830

create tablespace ts_logdetail_20120831 logging datafile 'E:\oracle\product\10.2.0\oradata\mobilemsg\sendlogdetail\logdetail_data_2012-08-31.dbf' size 10m autoextend on next 5m maxsize 100m

alter table SEND_LOG_DETAIL add partition partition_logdetail_20120831 values less than (to_date('2012-08-31','YYYY-MM-DD')) tablespace ts_logdetail_20120831

分享到：