oracle建立数据集,oracle建立数据库

CREATE tablespace drugdata DATAFILE

'd:\tablespace\drugdata.dbf' SIZE 4000m ONLINE;

CREATE tablespace drugtemp DATAFILE

'd:\tablespace\drugtemp.dbf' SIZE 500m ONLINE;

create tablespace tablespace_name datafile

'd:\drugdata\drugdata.dbf' size 1000M;

//查看表空间及空余空间大小

select

a.file_id,a.tablespace_name,a.file_name,a.bytes,b.free,a.autoextensible

from sys.dba_data_files a,(select file_id,sum(bytes) free from

sys.dba_free_space group by file_id) b where

a.file_id=b.file_id;

//更改表空间大小

alter database datafile 'D:\ORACLE\ORADATA\YDW\SYSTEM01.DBF'

resize 5000m;

//改变表空间自动扩展

alter database datafile 'D:\Oracle\ORADATA\ydwdb\system01.DBF'

autoextend on;

//指定表空间扩展大小

alter database datafile 'D:\Oracle\ORADATA\ydwdb\system01.DBF'

autoextend on next 10M maxsize 1000M;

//合并表空间碎片

alter tablespace tablespace_name coalesce;

//删除表空间(关闭sqlplus后才可删除文件)

drop  tablespace tablespace_name including

contents cascade constraints;

//给表空间增加数据文件

alter tablespace tablespace_name add datafile

'D:\Oracle\ORADATA\ydwdb\system02.dbf' size 1000m;

create table client (

name varchar2 (12) not null,

money number (16,6) default 0.000000,

currntdate date default sysdate

);

drop user ymdrug301 cascade; 删除用户。

//用bde修改表名，重新创建，然后insert

rename drugbzold to drugbzold2;

//给表增加字段

alter table drugzj add(drugbzbh VARCHAR2(20) default

'');

//修改表字段

alter table drugzj modify(drugbzbh VARCHAR2(50));

//删除表字段

alter table drugzj drop column drugbzbh;

//创建删除索引

create index drugbz_drugid_drugbbh on

drugbz(drugid,drugbbh);

drop index drugbz_drugid_drugbbh;

//创建删除主键

alter table drugbz add CONSTRAINT drugidbbh primary key

(drugid,drugbbh) using index;

alter table drugbz drop constraint drugidbbh cascade;

//创建用户并赋权

create user user_name identified by hiscss default tablespace

drugdata

temporary tablespace drugtemp

quota unlimited on drugdata;

grant resource,connect,dba to user_name;

//删除用户(包括该用户的表)

drop user user_name cascade;

//查看用户默认表空间、默认临时表空间

select username,default_tablespace,temporary_tablespace from

dba_users;

//修改用户默认表空间、默认临时表空间

alter user ydw default tablespace drugdata temporary

tablespace drugdatatemp;

//导出数据库，exp单步执行(dos下执行)

exp lrtpa/hiscss@oracle file=e:\back.dmp

//导入数据库，imp单步执行(dos下执行)

imp lrtpa/hiscss@oracle file=e:\back.dmp full=y

log=c:\a.txt

//日期型数据条件写法

select * from czrz where optdate =

to_date('2003/08/21','yyyy/mm/dd');

select * from czrz where to_char(optdate,'yyyy/mm/dd') =

'2003/08/21';

//检索相同名称，不同id的记录

select * from (select drugtym,count(*) as num1 from drugname

group by drugtym) a where a.num1>1;

//字段解释

select drugtym,decode(issf,'0','无效','有效') 是否有效 from

drugname;

//字段连接

select (drugtym||'('||drugid||')'||drugsf) 药品信息 from

drugname;

//判空nvl(exp1,exp2)若exp1不空返回exp1，否则返回exp2

select nvl(drugtym,'未知药品') 药品通用名 from drugname;

//取得不重复记录

select drugtym from drugname union select drugtym from

drugname;

//插入表数据

insert into drugbzold (select drugid,drugbbh,drugfile,drugbz,0

from drugbz);