当前位置:Gxlcms > mysql > Oracle使用

Oracle使用

时间:2021-07-01 10:21:17 帮助过:54人阅读

* sqlplus 不支持历史命令上翻下翻功能 执行上一次命令可通过/键来执行,但使用还是不方便 解决办法: 安装 rlwrap 1) sudo apt-get install rlwrap 2) 在 ~/.bashrc中加入 alias sqlplus='rlwrap sqlplus' * 查看所有用戶 SQL select username from dba_user

* sqlplus 不支持历史命令上翻下翻功能

执行上一次命令可通过"/"键来执行,但使用还是不方便

解决办法: 安装 rlwrap

1) sudo apt-get install rlwrap

2) 在 ~/.bashrc中加入

alias sqlplus='rlwrap sqlplus'

* 查看所有用戶

SQL> select username from dba_users;
SQL> select username from all_users;


* 显示当前用户

SQL> show user;

* 添加用户/删除用户

添加:

create user USER1 identified by Password;

grant connect,resource,dba to USER1;

drop user USER1 cascade;

* 授权/取消授权

eg:

grant dba to USER1;

revoke dba from USER1;


*取消用户锁定

alter user USER1 account unlock;


* 查看当前用户所有表

select table_name from user_tables;

* 查看表结构

desc tablename;

* 查看oralce中所有的系统权限,一般是dba

select * from system_privilege_map order by name;


* 查看oracle中所有的角色,一般是dba

select * from dba_roles;


* 查看数据库的表空间

select tablespace_name from dba_tablespaces;


* 查看所有对象权限

select distinct privilege from dba_tab_privs;


* 查看oracle数据块大小

show parameter db_block_size;


* "sqlplus / as sysdba" 不用输入用户名密码也能登录

原因: 没有使能认证方式

修改(有些问题,重启系统后数据库没启动,以后再查原因了): 使能认证方式,基于oracle认证

在 $ORACLE_HOME/network/admin/sqlnet.ora中加入: "sqlnet.authentication_services= (NONE)"


* sqlplus中打开计时功能,显示sql语句执行时间:

set timing on;


* sqlplus中循环插入语句

* 查看数据库大小

表: dba_data_files 记录了数据文件的详细信息,可通过该表查看数据库大小

表定义如下:

* oracle备份程序: exp

oracle恢复程序: imp

* sqlplus 提交事务: commit

事务回滚: rollback

只读事务: set transaction read only

* 查看数据库是否为归档模式:

select name,log_mode from v$database;

* oracle 官方文档

oracle官方文档在网站上的路径太深了:

www.oracle.com ->

support:Documention ->

选择自己要查找的版本,如"Oracle Database 11g Release 2" ->

"View Library" ->

http://www.oracle.com/pls/db112/homepage

还是记下上面的网址好,需要不容版本时,把"db112"进行相应切换

* 对于media failure/block等错误,可尝试用' rman: recover datafile "文件名" '来修复,如

RMAN> recover datafile "/home/oracle/oradata/mydb/sysaux01.dbf";

* 查看当前trace文件:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

* 查看当前用户session id:

SELECT USERENV('SID') FROM DUAL;

* 查看object id:

select object_id from user_objects where object_name='TEST_TABLE'; ##表名一定要大写

* 好的参考网站:

(1) http://www.juliandyke.com/index.htm

Julian.Dyke 的个人网站,有很多有用的东西

* 获取当前session的信息: select useenv('parameter') from dual;

parameter:

Parameter Return Value
CLIENT_INFO CLIENT_INFO returns up to 64 bytes of user session information that
can be stored by an application using the DBMS_APPLICATION_INFO
package.
Caution: Some commercial applications may be using this context
value. Refer to the applicable documentation for those applications to
determine what restrictions they may impose on use of this context
area.
ENTRYID The current audit entry number. The audit entryid sequence is shared
between fine-grained audit records and regular audit records. You
cannot use this attribute in distributed SQL statements.
ISDBA ISDBA returns 'TRUE' if the user has been authenticated as having
DBA privileges either through the operating system or through a
password file.
LANG LANG returns the ISO abbreviation for the language name, a shorter
form than the existing 'LANGUAGE' parameter.
LANGUAGE LANGUAGE returns the language and territory used by the current
session along with the database character set in this form:
language_territory.characterset
SESSIONID SESSIONID returns the auditing session identifier. You cannot specify
this parameter in distributed SQL statements.
SID SID returns the session ID.
TERMINAL TERMINAL returns the operating system identifier for the terminal of
the current session. In distributed SQL statements, this parameter
returns the identifier for your local session. In a distributed
environment, this parameter is supported only for remote SELECT
statements, not for remote INSERT, UPDATE, or DELETE operations.
19 Direct Load
20 Transaction Metadata (LogMiner)
22 Space Management (ASSM)
23 Block Write (DBWR)
24 DDL Statement

Examples
The following example returns the LANGUAGE parameter of the current session:
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
Language
-----------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

* 查看当前用户及UID: select user,uid from dual;

* 启动Oracle Web管理服务:

程序执行后会显示管理页面的URL,如:

https://duanbb:1158/em/console/aboutApplication

* SCN和timestamp之间的相互转换:

scn->timestap

timestamp->scn:

* ROWID


* 序列

1) 查询当前序列

SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM 
USER_SEQUENCES;
2) 创建序列

create sequence sequence_name;

3) 删除序列

drop sequence sequence_name;

*查看Oracle版本

select * from v$version;
select version from PRODUCT_COMPONENT_VERSION where rownum = 1;


人气教程排行