本文同步于个人Github博客:https://github.com/johnnian/Blog/issues/3,欢迎留言。
登录指令
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> @<PATH> --例如: @‘/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 <table_name> -- desc "test_table"
相关链接
- Oracle数据库,实例,表空间,用户,表之间的关系简析:链接
网友评论