oracle 数据库基本操作

来源：互联网发布：三星集团的成功知乎编辑：程序博客网时间：2024/06/06 09:12

--创建表空间
create tablespace STU
logging
datafile 'F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\LZGS\STU.DBF'
size 1024m
autoextend on
next 100m maxsize unlimited
extent management local;

--索引表空间
create tablespace STU_IND
logging
datafile 'F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\LZGS\STU_IND.DBF'
size 1024m
autoextend on
next 100m maxsize unlimited
extent management local;

--创建用户
create user stu identified by stu default tablespace STU;

--给用户授予管理员的权限
grant dba to stu;

--导入数据库
imp stu/stu@STU file=d:/20120704.pmd ignore=y

--导出数据库
exp stu/stu@STU file=d:\daochu.pmd full=y

--按用户导入导出
exp username/password owner=username file=d:\daochu.dmp(只能在服务器的机子上操作)
imp username/password fromuser=username touser=username file=d:\daochu.dmp

--查询触发器
select * from dba_triggers where owner='username';

select * from dba_objects where owner='username' and object_type='TRIGGER';

--拆分分区表
alter table T_BASEINFO split partition BASE_MAX at ('20130801000000000') into
(
partition BASE_201307 tablespace LZGS,
partition BASE_MAX tablespace LZGS
);

--建分区表
create table T_BASEINFO
(
baseid      NUMBER(10) not null,
gen_time    VARCHAR2(17),
filelogtime DATE default sysdate,
ch0101_w    NUMBER(10,3),
ch0101_d    NUMBER(10,3),
ch0102_w    NUMBER(10,3),
ch0102_d    NUMBER(10,3),
ch0103_w    NUMBER(10,3),
ch0103_d    NUMBER(10,3),
ch0104_w    NUMBER(10,3),
ch0104_d    NUMBER(10,3),
ch0201_w    NUMBER(10,3),
ch0201_d    NUMBER(10,3),
ch0202_w    NUMBER(10,3),
ch0202_d    NUMBER(10,3),
ch0203_w    NUMBER(10,3),
ch0203_d    NUMBER(10,3),
ch0204_w    NUMBER(10,3),
ch0204_d    NUMBER(10,3),
ch0301_w    NUMBER(10,3),
ch0301_d    NUMBER(10,3),
ch0302_w    NUMBER(10,3),
ch0302_d    NUMBER(10,3),
ch0303_w    NUMBER(10,3),
ch0303_d    NUMBER(10,3),
ch0304_w    NUMBER(10,3),
ch0304_d    NUMBER(10,3),
ch0105_w    NUMBER(10,3),
ch0105_d    NUMBER(10,3),
ch0106_w    NUMBER(10,3),
ch0106_d    NUMBER(10,3),
ch0401_w    NUMBER(10,3),
ch0401_d    NUMBER(10,3),
ch0402_w    NUMBER(10,3),
ch0402_d    NUMBER(10,3),
ch0403_w    NUMBER(10,3),
ch0403_d    NUMBER(10,3),
ch0404_w    NUMBER(10,3),
ch0404_d    NUMBER(10,3),
ch0501_w    NUMBER(10,3),
ch0501_d    NUMBER(10,3),
ch0502_w    NUMBER(10,3),
ch0502_d    NUMBER(10,3),
ch0503_w    NUMBER(10,3),
ch0503_d    NUMBER(10,3),
ch0504_w    NUMBER(10,3),
ch0504_d    NUMBER(10,3),
ch0505_w    NUMBER(10,3),
ch0505_d    NUMBER(10,3),
ch0506_w    NUMBER(10,3),
ch0506_d    NUMBER(10,3),
ch0507_w    NUMBER(10,3),
ch0507_d    NUMBER(10,3),
ch0508_w    NUMBER(10,3),
ch0508_d    NUMBER(10,3),
ch0509_w    NUMBER(10,3),
ch0509_d    NUMBER(10,3),
ch0510_w    NUMBER(10,3),
ch0510_d    NUMBER(10,3),
ch0511_w    NUMBER(10,3),
ch0511_d    NUMBER(10,3),
ch0512_w    NUMBER(10,3),
ch0512_d    NUMBER(10,3),
ch0513_w    NUMBER(10,3),
ch0513_d    NUMBER(10,3),
ch0514_w    NUMBER(10,3),
ch0514_d    NUMBER(10,3),
ch0601_w    NUMBER(10,3),
ch0601_d    NUMBER(10,3),
ch0602_w    NUMBER(10,3),
ch0602_d    NUMBER(10,3),
ch0603_w    NUMBER(10,3),
ch0603_d    NUMBER(10,3),
ch0604_w    NUMBER(10,3),
ch0604_d    NUMBER(10,3),
ch0605_w    NUMBER(10,3),
ch0605_d    NUMBER(10,3),
ch0606_w    NUMBER(10,3),
ch0606_d    NUMBER(10,3),
ch0607_w    NUMBER(10,3),
ch0607_d    NUMBER(10,3),
ch0608_w    NUMBER(10,3),
ch0608_d    NUMBER(10,3),
ch0609_w    NUMBER(10,3),
ch0609_d    NUMBER(10,3),
ch0610_w    NUMBER(10,3),
ch0610_d    NUMBER(10,3),
ch0611_w    NUMBER(10,3),
ch0611_d    NUMBER(10,3),
ch0612_w    NUMBER(10,3),
ch0612_d    NUMBER(10,3),
ch0613_w    NUMBER(10,3),
ch0613_d    NUMBER(10,3),
ch0614_w    NUMBER(10,3),
ch0614_d    NUMBER(10,3),
ch0615_w    NUMBER(10,3),
ch0615_d    NUMBER(10,3),
ch0616_w    NUMBER(10,3),
ch0616_d    NUMBER(10,3)
)
partition by range(gen_time)
(
partition base_201206 values less than ('20120701000000000') tablespace STU ,
partition base_201207 values less than ('20120801000000000') tablespace STU ,
partition base_201208 values less than ('20120901000000000') tablespace STU ,
partition base_201209 values less than ('20121001000000000') tablespace STU ,
partition base_201210 values less than ('20121101000000000') tablespace STU ,
partition base_201211 values less than ('20121201000000000') tablespace STU ,
partition base_201212 values less than ('20130101000000000') tablespace STU ,
partition base_201301 values less than ('20130201000000000') tablespace STU ,
partition base_201302 values less than ('20130301000000000') tablespace STU ,
partition base_201303 values less than ('20130401000000000') tablespace STU ,
partition base_201304 values less than ('20130501000000000') tablespace STU ,
partition base_201305 values less than ('20130601000000000') tablespace STU,
partition base_201306 values less than ('20130701000000000') tablespace STU ,
partition base_max values less than (maxvalue) tablespace STU
);