Oracle基本查询命令

Oracle基本查询命令

1.lsnrctl和tnsping

lsnrctl命令：

start 启动指定的监听器

stop 关闭指定的监听器

status 显示监听器的状态。status命令显示监听器是不是活动的，日志与跟踪文件的位置，监听器已经持续运行了多长时间，以及监听器所监听的任务。

C:\Users\Administrator>lsnrctl stat

　　

 

tnsping命令

测试数据库服务的命令：

命令格式：

tnsping IP地址:端口号/数据库服务名 [count]

tnsping 网络服务名 [count]

比如：tnsping localhost:1521/study

tnsping studydb

• tnsping命令：如果能够ping通，则说明客户端能解析listener的机器名，而且listener也已经启动，但是并不能说明数据库已经打开，而且tsnping的过程与真正客户端连接的过程也不一致。但是如果不能用tnsping通，则肯定连接不到数据库。

C:\Users\Administrator>tnsping 192.168.10.98 1521

　　

 

数据库启动

SQL> startup nomount        //启动实例，不启动数据库
SQL> startup mount          //启动实例，加载数据库，但不打开
SQL> startup                //启动实例，加载数据库，且打开
​
SQL> alter database mount   //前提是在nomount状态下
SQL> alter database open    //前提是mount状态
​
SQL> shutdown immediate     //
SQL> shutdown abort

 

2.oracle语句

三种身份认证方式：操作系统身份认证、密码文件认证、数据库认证。而conn /as sysdba是属于操作系统认证。

参数

v$instance 数据库实例信息
存放的是连接数据库实例的信息
--------------------------------------------------------------------
以下是oracle数据库提供的系统表：
dba_开头.....
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息
dba_ts_quotas 用户表空间配额信息
dba_free_space数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs用户具有的对象权限信息
dba_col_privs用户具有的列对象权限信息
dba_role_privs用户具有的角色信息
dba_audit_trail审计跟踪记录信息
dba_stmt_audit_opts审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session会话审计结果信息
dba_indexes用户模式的索引信息
​
user_开头
user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息
​
v$开头
v$database 数据库信息
v$datafile 数据文件信息
v$controlfile控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息
v$sysstat 数据库系统状态信息
​
all_开头
all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的表对象信息
all_indexes所有的数据库对象索引的信息
​
session_开头
session_roles 会话的角色信息
session_privs 会话的权限信息
​
index_开头
index_stats 索引的设置和存储信息
​
​
伪表
dual 系统伪列表信息
​
V$SHARED_POOL_RESERVED      显示有助于调整共享池中保留的池和空间的统计数据。

　　

2.1登录数据库

C:\Users\Administrator>sqlplus /nolog       //进入oracle软件，不连接数据库
> conn /as sysdba               //sqlplus  sys/password  as  sysdba
> select * from v$instance;

　　

显示当前数据库的全称

SQL> select * from global_name;

　　

查看当前的所有数据库:

SQL> select * from v$database;
SQL> select dbid,name,created from v$database;

　　

查看当前数据库所有表

SQL> select table_name from all_tables;

 

2.2登录普通用户

SQLPLUS 用户名/密码@111.111.111.111:1521/test

 

2.3创建用户

2.3.1创建临时表空间

创建临时表空间

SQL> create temporary tablespace
  2  synchromobile_temp
  3  tempfile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYNCHROMOBILE_TEMP.DBF‘
  4  size 50M
  5  autoextend on
  6  next 50M
  7  maxsize 500M
  8  extent management local;

​
查看临时表

SQL> select * from v$tempfile;

查看表空间名

SQL> select name from v$tablespace;
SQL> select tablespace_name from Dba_tablespaces;

​
ps：创建用户之前要先创建临时表空间，若不创建则默认的临时表空间为TEMP，Oracle下路径格式–\home\app\oracle\oradata\ORCL\CEPSP.DBF。

2.3.2创建（数据）表空间

创建（数据）表空间

SQL> CREATE TABLESPACE
        SYNCHROMOBILE 
        DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYNCHROMOBILE.DBF' 
        SIZE 50M 
        AUTOEXTEND ON 
        NEXT 50M 
        MAXSIZE 500M 
        EXTENT MANAGEMENT LOCAL;
​

ps：创建用户之前要先创建数据表空间，若不创建则默认的数据表空间是system。

 

2.3.3表删除操作

删除数据文件

alter tablespace 表空间名 drop datafile '数据文件路径';

​
删除临时表文件

alter tablespace 临时表空间名 drop tempfile '临时文件位置';
alter  database tempfile '临时文件路径' drop including datafiles;

​
删除表空间

SQL> drop tablespace synchromobile_temp including contents and datafiles cascade constraint;

​
--删除用户，及级联关系也删除掉（用户名添加双引号）

drop user 用户名称 cascade;

​
--查找用户

select  *　from dba_users;

​
--查找工作空间的路径

select * from dba_data_files; 

 

2.4创建用户并指定表空间

SQL> create user synchromobile_user     //用户名
  2  identified by synchromobile_user       //密码
  3  account unlock                     //解锁用户
  4  default tablespace synchromobile       //指定默认表空间
  5  temporary tablespace synchromobile_temp;   //指定临时表空间

　　

2.5给用户授权

grant connect,resource to synchromobile_user;
//登陆用需要create session权限

　　

2.6用户相关操作

查看所有用户

> select * from all_users;

　　

查看当前用户

> show user
> select user from dual;

　　

查看表空间及文件路径

（数据）表

SQL> SELECT * FROM DBA_DATA_FILES;
或
SQL> select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

​
临时表及路径

SQL> SELECT * FROM DBA_TEMP_FILES;
或
SQL> select t1.name,t2.name from v$tablespace t1,v$tempfile t2 where t1.ts# = t2.ts#;

 

查看用户所使用的表空

SQL> select default_tablespace from dba_users where username='SYNCHROMOBILE_USER';

　　

修改用户名

查看用户相关信息（user#）
SQL> select user#,name from user$;
或
SQL> select username,user_id from all_users;
​
修改用户名并提交
SQL> update user$ set name='zhangsan' where user#=90;
SQL> commit;

强制刷新

SQL> alter system checkpoint;
SQL> alter system flush shared_pool;

登录(此处若不添加双引号，会被认为为ZHANGSAN，导致登陆失败)

SQL> conn "zhangsan"/123456

ps:修改用户名后，需严格对用户名的大小写进行输入，双引号严格区分大小写，默认为大写，单引号不区分大小写。

删除表空间

SQL> DROP TABLESPACE SYNCHROMOBILE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINT;
//CASCADE CONSTRAINT表示级联关系也删除掉

　　

查看用户权限

//查看用户系统权限
SQL> select * from dba_sys_privs;
//查看用户角色权限
SQL> select * from dba_role_privs;
//当前用户角色权限
SQL> select * from user_role_privs; 
//查看某个用户拥有的权限
SQL> select * from dba_sys_privs where grantee='zhaoliu';
//查看某个用户拥有的角色
SQL> select * from dba_role_privs where grantee='zhaoliu';

 

2.7查看字符集

SQL> select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';
或
SQL> select * from v$nls_parameters  where parameter='NLS_CHARACTERSET'; 
​
SQL> select userenv('language') from dual;
​
USERENV('LANGUAGE')
-------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8