一些oracle命令1

----建表
CREATE TABLE STUDENT
( ID NUMBER(38) PRIMARY KEY,
  NAME VARCHAR2(30),
  CODE VARCHAR2(9),
  SEX NUMBER(1),
  PHONE VARCHAR2(13),
  ADDRESS VARCHAR2(100),
  EMAIL VARCHAR2(50)
)
----删除表
DROP TABLE STUDENT
----修改表的某一列
ALTER TABLE STUDENT MODIFY NAME VARCHAR2(35)
----添加新列
ALTER TABLE STUDENT ADD(XX1 VARCHAR2(10),XX2 VARCHAR2(10))
----删除列
ALTER TABLE STUDENT DROP(XX1,XX2)
----创建新的SEQUENCE
CREATE SEQUENCE WXJSEQ INCREMENT BY 1 START WITH 1
SELECT WXJSEQ.NEXTVAL FROM DUAL

----插入新的记录
INSERT INTO STUDENT VALUES  (WXJSEQ.NEXTVAL,'王先江', '011150232', '0', '13235915923', '福州市西洪路西洪花园4#603', 'feixiange23@163.com')
--删除表的所有记录,不释放空间，会产生碎片  
DELETE STUDENT
----删除记录
DELETE FROM STUDENT
----删除表的所有记录，释放空间，不会产生碎片
TRUNCATE TABLE STUDENT 
----删除索引
DROP INDEX IDX_STUDENT_NAME 
----建立索引
CREATE INDEX IDX_STUDENT_NAME ON STUDENT(NAME ASC)
----删除视图
DROP VIEW SVIEW
----创建新视图
CREATE VIEW SVIEW AS (SELECT * FROM STUDENT WHERE SEX = 0)
SELECT * FROM SVIEW

SELECT * FROM STUDENT
SELECT S.* FROM (SELECT ROWNUM RN,S.* FROM STUDENT S WHERE ROWNUM <=3) S WHERE S.RN >1

---创建一个指向10g数据库的dblink
---using后接的是目标数据库IP、监听端口、服务名。如果监听端口是1521的话可以省略不写。
---创建的连接的名称为 suk10g，访问数据库的用户名为 suk，密码为：suk
create database link suk10g connect to suk identified by suk using '172.25.4.89:1522/ora10g'
----远程数据库服务名：SKY,用户名:先江,密码：SKY
CREATE DATABASE LINK LINKSKY CONNECT TO XIANJIANG IDENTIFIED BY SKY USING '192.168.1.100:1521/SKY'