oracle基础

 

1. 查询服务名
   SELECT GLOBAL_NAME FROM GLOBAL_NAME;

2. 创建用户
   CREATE USER [USER_NAME] IDENTIFIED by [PASSWORD];

3. 授权
   grant connect,resource to [user_name]; //给用户[usernma]授权

4. 创建表空间
   CREATE TABLESPACE [TABLESPACE_NAME] DATAFILE 'd:\**.dbf' SIZE 512M; //TABLESPACE_NAME为表空间名字，'d:\**.dbf'为表空间存储路径

5. 修改用户[USER_NAME] 的默认表空间
   alter user [USER_NAME] default tablespace [TABLESPACE_NAME];

6. 提交
   commit;

7. 解锁
   ALTER USER [user_name] ACCOUNT UNLOCK;

8. 修改用户密码
   ALTER USER [USER_NAME] IDENTIFIED BY 新密码;

============================================================================================
1. 增加主键
   CONSTTAINT [主键名] PRIMARY KEY ([字段名]);

2. 增加外键
   CONSTRAINT [外键名] FOREIGN KEY ([字段名]) REFERENCE [引用表名] ([引用表字段名]);

3. 创建索引
   create index index_name on [表名] ([字段名]);

4. 创建视图
   create or repeace view [view_name] AS select [name,sex] from [student];

5. %TYPE
   DECLARE V_FIRSTNAME STUDENTS.FIRST_NAME%TYPE;

6. 添加字段说明
ALTER TABLE ECC_OSN.FIX_BALANCE_PRINT_PROP ADD TELEGRAPHIC VARCHAR2(1000);
COMMENT ON COLUMN ECC_OSN.FIX_BALANCE_PRINT_PROP.TELEGRAPHIC IS "电汇"

 

查询某个字段在哪张表
SELECT * FROM ALL_TAB_COLUMNS A WHERE A.COLUMN_NAME = '字段名';