oracle命令窗口命令,Oracle 命令行小结

登录指令

sqlplus /*运行SQLPLUS工具*/

sqlplus /nolog /*直接进入SQLPLUS命令提示符*/

sqlplus / as sysdba /*以OS身份连接*/

sqlplus scott/123456 /*普通用户登录*/

sqlplus sys/123456 as sysdba /*以管理员登录*/

SQL>conn hr/123456 /*切换用户*/

quit /*断开连接*/

用户创建/删除

/*步骤1:删除 root 用户*/

drop user root cascade;

/*步骤2:删除 TABLE_SPACE_TMP 临时表空间*/

drop tablespace TABLE_SPACE_TMP including CONTENTS and datafiles;

/*步骤3:删除 TABLE_SPACE 表空间*/

drop tablespace TABLE_SPACE including CONTENTS and datafiles;

/*步骤4:创建临时表空间*/

create temporary tablespace TABLE_SPACE_TMP tempfile '/home/oracle/oradata/temp.dbf' size 128M autoextend on next 128M maxsize 512M extent management local;

/*步骤5:创建表空间*/

create tablespace TABLE_SPACE logging datafile '/home/oracle/oradata/tbs.dbf' size 512M;

/*步骤6:创建 root 用户、密码 root， 表空间分配*/

create user root identified by root default tablespace TABLE_SPACE temporary tablespace TABLE_SPACE_TMP;

/*步骤7:创建表空间*/

grant connect,resource,dba to root;

/*步骤8:提交*/

commit;

常用指令

/*查看当前数据库名*/

SQL> select name from v$database;

SQL> desc v$database; --查看表结构

/*查看当前实例名*/

select instance_name from v$instance;

SQL> desc v$instance; --查看表结构

/*运行SQL脚本*/

SQL> @ --例如： @‘/home/oracle/test.sql’

/*获取表字段*/

select *

from user_tab_columns

where Table_Name='用户表'

order by column_name

/*获取表注释*/

select *

from user_tab_comments

where Table_Name='用户表'

order by Table_Name

/*获取字段注释*/

select *

from user_col_comments

where Table_Name='用户表'

order by column_name

/* 获取表：*/

select table_name from user_tables; --当前用户的表，属性有 table_name,tablespace_name,last_analyzed等

select table_name from all_tables; --所有用户的表，属性有 ower,table_name,tablespace_name,last_analyzed

select table_name from dba_tables; --包括系统表，属性有 ower,table_name,tablespace_name,last_analyzed

select table_name from dba_tables where owner='zfxfzb'

/* 获取表字段：*/

select * from user_tab_columns where Table_Name='用户表'; --table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id

select * from all_tab_columns where Table_Name='用户表'; --ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id

select * from dba_tab_columns where Table_Name='用户表'; --ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id

/* 获取表注释：*/

select * from user_tab_comments --table_name,table_type,comments

/* 获取字段注释：*/

select * from user_col_comments --table_name,column_name,comments

/* 删除用户所有表 */

/*将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了。*/

select 'drop table '||table_name||';'

from cat

where table_type='TABLE'

/* 查看表结构： 注意，表名需加引号*/

desc -- desc "test_table"

相关链接

Oracle数据库，实例，表空间，用户，表之间的关系简析：链接