Oracle SqlPlus常用操作, oracle, sqlplus, sql

sqlplus.md

在oracle用户下使用sqlplus登录数据库

$ sqlplus / as sysdba
或
$ sqlplus /nolog

SQL> conn / as sysdba
Connected.
SQL> SHOW USER
USER is "SYS"

SQL> conn sys/pwd as sysdba
SQL> conn bys/pwd

设置sqlplus的屏显

# 设置行宽
$ sqlplus /nolog
SQL> SHOW LINESIZE
linesize 80
SQL> SET LINESIZE 500

# 设置分页行数
SQL> SHOW PAGESIZE
pagesize 14
SQL> SET PAGESIZE 50

SQL> SELECT table_name FROM user_tables;
# 设置指定列名的显示宽度(a20表示20个字符宽度)
SQL> col table_name format a20

# 显示列的当前的显示属性
SQL> CLOUMN table_name

# 恢复指定列名的显示宽度为默认值
SQL> COL table_name clear

# 将所有列的显示属性设为缺省值 
SQL> CLEAR columns

启动和关闭数据库

--查询Oracle监听器状态并启动
$ lsnrctl status
$ lsnrctl start

--使用sysdba登入sqlplus启动或关闭oracle
sqlplus / as sysdba
SQL> startup
SQL> shutdown immediate

查询系统用户

 * FROM dba_users;   
SELECT * FROM all_users;   
SELECT * FROM user_users;

查看用户或角色系统权限

SELECT * FROM dba_sys_privs;
-- 查看当前用户所拥有的权限
SELECT * FROM user_sys_privs; 

查询本数据库所有表信息

--查看当前用户所拥有的表： 
SELECT table_name FROM user_tables;
--用户可存取的表
SELECT table_name FROM all_tables;
--数据库中所有表
SELECT table_name FROM dba_tables;

查询表字段信息

DESC user_tables;
SELECT column_name, data_type FROM user_tab_columns where table_name='product';

查询当前数据库名(db_name)

SELECT name FROM v$database;
--或
SHOW PARAMETER db
--或
SHOW PARAMETER db_unique_name
--或
查看参数文件，$ORACLE_HOME/admin/db_name/pfile/init.ora（或$ORACLE_BASE/admin/db_name/pfile/init.ora）

查询当前数据库实例名(instance_name, 对应操作系统中的环境变量ORACLE_SID)

SELECT instance_name FROM v$instance;
--或
SHOW PARAMETER instance
--或
SHOW PARAMETER instance_name

创建新的Schema

使用sqlplus / as sysdba登录数据库并执行下面的SQL语句

1. 创建相应的用户

CREATE USER smithj
      IDENTIFIED BY pwd4smithj
      DEFAULT TABLESPACE tbs_perm_01
      TEMPORARY TABLESPACE tbs_temp_01
      QUOTA 20M on tbs_perm_01;

2. 给新建用户授权

GRANT CREATE SESSION TO smithj;
GRANT CREATE TABlE TO smithj;
GRANT CREATE VIEW TO smithj;
GRANT CREATE ANY TRIGGER TO smithj;
GRANT CREATE ANY PROCEDURE TO smithj;
GRANT CREATE SEQUENCE TO smithj;
GRANT CREATE SYNONYM TO smithj;

3. 在新创建的用户Schema中创建对象

• CREATE TABLE Statements，为每一个数据执行单独的SQL语句
• CREATE SCHEMA Statements，在单个SQL中创建多个对象