|
ATAFILE B WHERE
A.TS#=B.TS#
GROUP BY A.NAME;
//查?表空?空?空?
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;
//?建??表空?
CREATE TEMPORARY TABLESPACE USER_TEMP
TEMPFILE 'F:\DATA_TEMP.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 10280M
EXTENT MANAGEMENT LOCAL;
//?建表空?
CREATE TABLESPACE USER_DATA
DATAFILE 'F:\USER_DATA.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 10280M
EXTENT MANAGEMENT LOCAL;
//?建用?并指定表空?
CREATE USER MYORCL IDENTIFIED BY ADMIN
DEFAULT TABLESPACE USER_DATA
TEMPORARY TABLESPACE USER_TEMP;
//CONN SYSTEM/ADMIN
//GRANT CONNECT,RESOURCE TO MYORCL
oracle调整表空间文件大小
1、调整表空间文件大小
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m;
2、调整表空间文件自动扩展
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on
next 20m maxsize 1g;
3、新增磁盘
alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on
next 20m maxsize 1g;
alter tablespace userdb add datafile '/u03/oradata/users02.dbf' size 50m
autoextend on next 10m maxsize 200m
5、修改表空?名
alter tablespace userdb rename to userdb2
//增加列
ALTER TABLE emp01 ADD eno NUMBER(4);
//修改列定义
ALTER TABLE emp01 MODIFY job VARCHAR2(15) ;
//删除列
ALTER TABLE emp01 DROP COLUMN dno;
//修改列名
ALTER TABLE emp01 RENAME COLUMN eno TO empno;
//修改表名
RENAME emp01 TO employee;
//修改默认表空间到指定表空间
ALTER DATABASE DEFAULT TABLESPACE USER_DATA;
//增加注释
COMMENT ON TABLE employee IS '存放雇员信息';
COMMENT ON TABLE employee.name IS '描述雇员姓名';
1、只复制表结构的sql
create table b as select * from a where 1<>1
2、即复制表结构又复制表中数据的sql
create table b as select * from a
3、复制表的制定字段的sql
create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列
4、复制表的指定字段及这些指定字段的数据的sql
create table b as select row_id,name,age from a
以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了，需要在b中手动建立。
5、insert into 会将查询结果保存到已经存在的表中
insert into t2(column1, column2, ....) select column1, column2, .... from t1
//SQL插入时间date类型
INSERT INTO PERSON (ID,NAME,AGE,BRITHDAY,MONEY) VALUES(
1,'张三',22, to_DATE('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0);
INSERT INTO PERSON (NAME,AGE,BRITHDAY,MONEY) VALUES(
'张三',22, to_DATE('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0);
//对PERSON 的ID进行自增
//?建序列
CREATE SEQUENCE SEQ_ID
START WITH 1 --?始值
MAXVAULE 99999999999999 --最大值 99999999999999
MINVAULE 1 --最小值1
NOCYCLE --序列到?最大值以後不再循?
CACHE 100; --?存100?序列值
CREATE SEQUENCE SEQ_ID
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;
CREATE OR REPLACE TRIGGER TRI_USERBEAN
BEFORE INSERT ON USERTEST
FOR EACH ROW
BEGIN
IF(:NEW.ID IS NULL)THEN
SELECT SEQ_USERBEAN.NEXTVAL INTO:NEW.ID FROM DUAL;
END IF;
END;
/
CREATE OR REPLACE TRIGGER TRI_PERSON
BEFORE INSERT ON PERSON
FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
SELECT SEQ_ID_PERSON.NEXTVAL INTO :NEW.ID FROM DUAL
END;
/
CREATE TRIGGER TRI_INS_PERSON BEFORE
INSERT ON PERSON FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
SELECT SEQ_ID_PERSON.NEXTVAL INTO : NEW.ID FROM DUAL
END;
//?建??器
CREATE OR REPLACE TRIGGER TRIGGER_PERSON
BEFORE INSERT ON PERSON --插入前??
FOR EACH ROW --?每行?? 自?增加
BEGIN
SELECT SEQ_ID.NEXTVAL INTO : NEW.ID FROM DUAL; --序列到下?值
END;
//?除TRIGGER
DROP TRIGGER TRIGGER_PERSON;
//禁用和启用触发器
alter trigger disable;
alter trigger enable;
//查看?前用??前表??器
SELECT * FROM USER_TRIGG |