自己这段时间在Linux上操作了一次Oracle，参考了很多资料，汇总记录一下。有部分内容是来自其他大神的博客，如有侵权，请联系我删除，谢谢！
通过Oracle用户连接
linux下查看oracle中有多少个实例已经启动了:
ps -ef|grep smon
echo命令可以查看变量
echo $ORACLE_HOME看看目录在哪
启动数据库实例，分为两步：
第一步，启动监听；
su - oracle --“切换到oracle用户”
lsnrctl start --“打开监听”
lsnrctl stop --“关闭监听”
lsnrctl status --“查看监听状态”
第二步，启动数据库实例；
1、设置默认的oracle_sid 可以用echo $ORACLE_SID查看当前默认的SID
export ORACLE_SID=SID2
2、运行sqlplus命令，进入到sqlplus环境(以不连接数据库的方式启动sqlplus)”
sqlplus /nolog
3、连接数据库(以管理员身份连接)，使用sys登录oracle在执行startup命令
conn /as sysdba
4、启动数据库实例的命令
startup
5、关闭数据库实例的命令
shutdown immediate
6、查看状态
select status from v
i
n
s
t
a
n
c
e
;
s
e
l
e
c
t
o
p
e
n
m
o
d
e
f
r
o
m
v
instance; select open_mode from v
instance;selectopenmodefromvdatabase;
7、连接指定的用户（显示connected表示连接成功）
conn username/password
配置环境变量：
[oracle@ZL-ZHB-ORADB01 ~]$ vim ~/.bash_profile
[root@ZL-ZHB-ORADB01 ~]# source ~/.bash_profile（使其生效）
手动创建数据库：
-
定义实例名、数据库名
实例名：DZHBDB
数据库名：DZHBDB -
创建目录
mkdir -p /u01/app/oracle/admin/DZHBDB/adump
mkdir -p /u01/app/oracle/oradata/DZHBDB/controlfile
mkdir -p /u01/app/oracle/oradata/DZHBDB/datafile
mkdir -p /u01/app/oracle/oradata/DZHBDB/onlinelog -
创建参数文件
vi /u01/app/oracle/product/dbs/initDZHBDB.oraDZHBDB.__db_cache_size=10938744832
DZHBDB.__java_pool_size=67108864
DZHBDB.__large_pool_size=335544320
DZHBDB.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
DZHBDB.__pga_aggregate_target=6308233216
DZHBDB.__sga_target=18857590784
DZHBDB.__shared_io_pool_size=0
DZHBDB.__shared_pool_size=7314866176
DZHBDB.__streams_pool_size=67108864
*.audit_file_dest=’/u01/app/oracle/admin/DZHBDB/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/u01/app/oracle/oradata/DZHBDB/controlfile/control01.ctl’,’/u01/app/oracle/oradata/DZHBDB/controlfile/control02.ctl’,’/u01/app/oracle/oradata/DZHBDB/controlfile/control03.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle/oradata/DZHBDB/datafile’
*.db_domain=’’
*.db_files=1024
*.db_name=‘DZHBDB’
*.db_writer_processes=6
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=DZHBDBXDB)’
*.filesystemio_options=‘ASYNCH’
*.job_queue_processes=0
*.log_checkpoints_to_alert=TRUE
*.open_cursors=300
*.open_links=0
*.pga_aggregate_target=11474836480
*.processes=1000
*.recovery_parallelism=0
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_max_size=18854445056
*.sga_target=18854445056
*.undo_tablespace=‘UNDOTBS’
*.use_large_pages=‘TRUE’ -
启动实例
sqlplus / as sysdba
startup nomount pfile=’/u01/app/oracle/product/dbs/initDZHBDB.ora’;
create spfile from pfile=’/u01/app/oracle/product/dbs/initDZHBDB.ora’;
shutdown immediate;
startup nomount; -
创建数据库
CREATE DATABASE DZHBDB
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo01.log’) SIZE 100M BLOCKSIZE 512,
GROUP 2 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo02.log’) SIZE 100M BLOCKSIZE 512,
GROUP 3 (’/u01/app/oracle/oradata/DZHBDB/onlinelog/redo03.log’) SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 1000
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/system01.dbf’ SIZE 325M REUSE
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/users01.dbf’ SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/temp01.dbf’ SIZE 20M
UNDO TABLESPACE undotbs DATAFILE ‘/u01/app/oracle/oradata/DZHBDB/datafile/undotbs01.dbf’ SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; -
创建视图和过程
conn / as sysdba
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/owminst.plb -
创建监听并启动
cd $ORACLE_HOME/network/admin
vi listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ZL-ZHB-ORADB01)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
lsnrctl start -
测试
sqlplus system/oracle@DZHBDB
select open_mode from v$database;
或
sqlplus system/oracle@ZL-ZHB-ORADB0/DZHBDB如sys用户无法登录，解决方法如下：
cd /u01/app/oracle/product/11.2.4/db_1/dbs
orapwd file=orapwDZHBDB
输入登录密码设置完成show parameter log_checkpoint
alter system set log_checkpoints_to_alert=FALSE; -
--追加表空间
alter tablespace sysaux add datafile '/u01/app/oracle/oradata/DZHBDB/datafile/sysaux02.dbf’size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;
alter tablespace system add datafile ‘/u01/app/oracle/oradata/DZHBDB/datafile/system02.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;
alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/DZHBDB/datafile/temp02.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;alter tablespace undotbs add datafile ‘/u01/app/oracle/oradata/DZHBDB/datafile/undotbs02.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;
alter tablespace users add datafile ‘/u01/app/oracle/oradata/DZHBDB/datafile/users02.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;–已有实例也需执行
alter tablespace users add datafile ‘/u01/app/oracle/oradata/DZHBDB/datafile/users03.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;–已有实例也需执行
alter tablespace users add datafile ‘/u01/app/oracle/oradata/DZHBDB/datafile/users04.dbf’ size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;–已有实例也需执行
导入Oracle的dmp文件
1、首先创建一个文件夹
create or replace directory dump_dir as ‘/u01/backup’;
2、给该文件夹授权
grant read,write on directory dump_dir to system;
3、导入dmp文件
impdp system/oracle directory=DUMP_DIR dumpfile=NEWE3SALL2.dmp logfile=NEWE3SALL2.log
4、还原数据库 用户名密码修改为实际值
impdp system/NEWE3SDB directory=DUMP_DIR dumpfile=NEWE3SALL.dmp logfile=NEWE3SALL.log REMAP_TABLESPACE=ADC_TBS:USERS,AI_DATA_TBS:USERS TABLE_EXISTS_ACTION=REPLACE;
5、停止触发器 测试限制语句
alter trigger TRG_FORBIDDEN_DATA1 disable;
alter trigger TRG_FORBIDDEN_DATA2 disable;
alter trigger TRG_FORBIDDEN_DATA3 disable;
alter trigger TRG_FORBIDDEN_PRIVS disable;
其他命令
-
OS版本及Oracle版本
select banner from v$version; -
查询该数据库的实例名
select name from v$database;select instance_name from v$instance;
-
查询该用户下所有的表
select * from tab; -
查看当前用户信息
select * from user_users; -
查看当前用户的角色
select * from user_role_privs; -
查看当前用户的权限
select * from user_sys_privs; -
查看当前用户的表可操作权限
select * from user_tab_privs; -
查看你能管理的所有用户
select * from all_users; -
删除用户
drop user JKW cascade; -
查询当前用户的缺省表空间
select username,default_tablespace from user_users; -
查询当前用户的角色
select * from user_role_privs; -
可以看到表内容
select * from dba_directories; -
查询所有表空间
select file_name,tablespace_name,bytes from dba_data_files; -
查看表空间的名称及大小:
SELECT T.TABLESPACE_NAME,ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME GROUP BY T.TABLESPACE_NAME; -
查看表空间物理文件的名称及大小:
SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME; -
查看所有表空间对应的数据文件名：
SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES; -
查看表空间的使用情况:
SELECT SUM(BYTES)/(1024*1024) AS FREE_SPACE,TABLESPACE_NAME
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES100)/A.BYTES “% USED”,(C.BYTES100)/A.BYTES “% FREE”
FROM SYS.SM T S A V A I L A , S Y S . S M TS_AVAIL A,SYS.SM TSAVAILA,SYS.SMTS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; -
查看日志文件:
SQL> COL MEMBER FORMAT A50
SQL>SELECT * FROM V$LOGFILE; -
查看当前数据库的字符集:
SQL>SELECT USERENV(‘LANGUAGE’) FROM DUAL;
SQL>SELECT USERENV(‘LANG’) FROM DUAL; -
查看客户端登陆的IP地址:
SQL>SELECT SYS_CONTEXT(‘USERENV’,‘IP_ADDRESS’) FROM DUAL; -
在生产数据库中创建一个追踪客户端IP地址的触发器：
CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’));
END; -
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER=‘WACOS’) --ONLY FOR USER ‘WACOS’
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT(‘USERENV’,‘IP_ADDRESS’),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV(‘SESSIONID’);
END; -
修改用户密码
查看所有用户名、密码
select username,password from dba_users;
修改用户密码
//修改用户口令 格式 alter user 用户名 identified by 新密码;
SQL> alter user system identified by manager; -
查询你 当前用户下,有哪些表
SELECT * FROM user_tables; -
查询你 当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables; -
查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables -
查询数据库表空间
select distinct TABLESPACE_NAME from tabs;create tablespace SJCJ_DAT DATAFILE ‘D:\app\oukf\virtual\oradata\orcl\SJCJ_DAT.DBF’ SIZE 2048M AUTOEXTEND ON NEXT 5M MAXSIZE 3072M;
-
修改表空间大小
ALTER TABLESPACE SYSTEM ADD DATAFILE ‘/u01/app/oracle/oradata/test/system02.dbf’ SIZE 20480M AUTOEXTEND OFF; -
创建用户并指定表空间
create user jkw identified by jkw123 default tablespace RMDB; -
给用户授予权限
grant connect,resource,dba to jkw;
grant dba to hc_notify;
grant connect,resource to hc_notify;
grant select any table to hc_notify;
grant delete any table to hc_notify;
grant update any table to hc_notify;
grant insert any table to hc_notify; -
查看临时表空间文件
select name from v$tempfile; -
查看用户和表空间的关系
select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS; -
如果有用户的默认临时表空间是NOTIFYDB_TEMP的话，建议进行更改
alter user xxx temporary tablespace tempdefault; -
设置tempdefault为默认临时表空间
alter database default temporary tablespace tempdefault; -
删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles; -
查看表空间文件
select name from v$datafile; -
停止表空间的在线使用
alter tablespace 表空间名称 offline; -
删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles; -
系统数据字典 DBA_TABLESPACES 中记录了关于表空间的详细信息
select * from sys.dba_tablespaces; -
查看用户序列
select * from user_sequences; -
查看数据库序列
select * from dba_sequences;
1615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
