oracle常用命令1

来源：互联网发布：万历朝鲜战争知乎编辑：程序博客网时间：2024/05/16 11:11

1、连接到oracle
                                      SQL*Plus system/manager
2、显示当前连接用户
                                      SQL> show user
3、查看系统拥有哪些用户
                                      SQL> select * from all_users;
4、新建用户并授权
                                      SQL> create user a identified by a;（默认建在SYSTEM表空间下）
                                      SQL> grant connect,resource to a;
5、连接到新用户
                                      SQL> conn a/a
6、查询当前用户下所有对象
                                      SQL> select * from tab;
7、建立第一个表
                                      SQL> create table a(a number);
8、查询表结构
                                      SQL> desc a
9、插入新记录
                                      SQL> insert into a values(1);
10、查询记录
                                      SQL> select * from a;
11、更改记录
                                      SQL> update a set a=2;
12、删除记录
                                      SQL> delete from a;
13、回滚
                                      SQL> roll;
                                      SQL> rollback;
14、提交
                                      SQL> commit;
15、用户授权:
                                      GRANT ALTER ANY INDEX TO "user_id "
                                      GRANT "dba " TO "user_id ";
                                      ALTER USER "user_id " DEFAULT ROLE ALL
16、创建用户:
CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DFAULT TABLESPACE " USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;
GRANT "CONNECT " TO "user_id ";
17、用户密码设定:
ALTER USER "CMSDB " IDENTIFIED BY "pass_word "
18、表空间创建:
CREATE TABLESPACE "table_space " LOGGING DATAFILE
'C:/ORACLE/ORADATA/dbs/table_space.ora' SIZE 5M

本文来自: 脚本之家(www.jb51.net) 详细出处参考：http://www.jb51.net/article/7827.htm