02_DBA Oracle

数库库启动关闭连接查看

# 连接Oracle实例
sqlplus / as sysdba
# 连接数据库命令方式（只能在数据库本机用）
sqlplus [username]/[password] [as] [sysdba/sysoper]
# 连接数据库-提示符方式（只能在数据库本机用）:conn dsdemo/dsdemo
SQL> conn [username]/[password] [as] [sysdba/sysoper]
# 连接数据库-连接字符串方式（只要配置了Net都可以用）: sqlplus ds/ds@topprd
sqlplus [username]/[password]@[servicename]

# 启动数据库至OPEN状态
SQL> startup
# 启动数据库至nomount状态
SQL> startup nomount
# 将数据库从nomount启动至mount状态
SQL> alter database topprd mount
# 将数据库以只读方式启动
SQL> alter database topprd open read only
# 数据库关闭
SQL> shutdown immediate

# 查看SGA分配情况
show sga
# Oracles查看后台进程
ps -ef | grep ora_
ps -ef | grep smon_
# 查看当前实例
echo $ORACLE_SID
# 查看ORACLE进程资源
top -u oracle

# T100重启Web service的操作方式
systemctl stop gas-topprd.service
systemctl stop gas-topprd-ws.service

systemctl start gas-topprd-ws.service
systemctl start gas-topprd.service

 

Oracle数据文件：

# .ctl、.dbf、.log三种文件通常放在一起
ls $ORACLE_BASE/oradata/$ORACLE_SID

# 参数文件放在另外的目录：inittopprd.ora spfiletopprd.ora
ls $ORACLE_HOME/dbs

# Net服务配置文件
cat tnsnames.ora

topprd = 
    (DESCRIPTION = 
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.70.102)(PORT = 1521))
            )
            (CONNECT_DATA = (SERVICE_NAME = topprd)
         )
     )

 测试监听

# Net测试工具tnsping topprd
tnsping [服务名]

# 监听管理工具：管理员需要对Oracle进行一些操作时，为了防止T100用户登录和使用，可以使用命令停掉监听器，这样可以在不关闭服务器的情况下避免客户端登入
# 先输入lsnrctl命令之后再输入相应的命令
lsnrctl[start/stop/status/reload/help][lsnr_name]

 SQLPLUS常用操作

# 显示sqlplus选项
SQL> show [option]
# 显示所有sqlplus选项,仅限于系统用户sysdba使用，支持模糊查询：show parameter sga
SQL> show parameters [param]

# 回滚提交操作
SQL> rollback
SQL> commit

# 执行Sql脚本
vi mysql.sql
delete from xx_file where cus01 = 'C002'
# 命令行方式
sqlplus ds/ds @mysql
# 提示符方式
SQL> @mysql;
# 显示上一句SQL
SQL> list
# 重新执行上一句SQL
SQL> / 或 SQL> r
# 进行OS命令行方式
SQL> host 或 !
# 直接执行OS命令
SQL> host 或 ![oscommond]
# 将当前SQL指令保存至文件
SQL> save [filename] [replace]
# 从文件中读取SQL
SQL> get [filename]
# 执行文件中的SQL（等同于@）
SQL> start [filename]
# 直接编辑文件
SQL> edit [filename]
# 将当前执行结果输出到文件中: spool output.log
SQL> spool [filename]
# 设定某个选项
SQL> set linesize 9999/set autocommit on/set autotrace on

# 创建表空间: create tablespace shitingchun datafile '/u2/oracle/shitingchun/shitingchun.dbf' size 10M autoextend on next 100M maxsize 200M;
SQL> CREATE TABLESPACE test DATAFILE '/u2/oracle/oradata/topprd/test-01.dbf' SIZE 20 AUTOEXTEND ON NEXT 100M MAXSIZE 200M;

# 为表空间增加文件: alter tablespace shitingchun add datafile '/u2/oracle/shitingchun/shitingchun02.dbf' size 20M;
SQL> ALTER TABLESPACE test ADD DATAFILE ‘$ORACLE_BASE/oradata/$ORACLE_SID/test-02.dbf’SIZE 20M;

# 删除表空间: drop tablespace shitingchun including contents and datafiles;
SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

# 创建临时表空间：create temporary tablespace temp_shitingchun tempfile '/u2/oracle/shitingchun/temp_shitingchun.dbf' size 10M autoextend on next 50M;
SQL> >CREATE TEMPORARY TABLESPACE temp02 TEMPFILE ‘u2/oradb/oradata/topprd/temp02.dbf’ SIZE 500M AUTOEXTEND ON NEXT 50M;

# 更改临时表空间：alter database default temporary tablespace temp_shitingchun;
# 当出现临时表空间过大时，不能使用删除文件的方法清空表空间，可先创建一个新的临时表空间，再将当前数据库的缺省临时表空间指定为新建的表空间，
# 此时就可以删除原有的超大文件
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp02;

#为表空间添加权限
create user shitingchun identified by password default tablespace shitingchun temporary tablespace temp_hitingchun

# 删除临时表空间步骤：
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';  #查询默认的表空间
SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u2/oradata/topprd/temp02.dbf' SIZE 16G AUTOEXTEND ON NEXT 30G;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

 

T00自带查询表空间的脚本

# T100自带sql脚本地址：/u1/etc/scripts/
# 使用脚本时必须使用管理员身份登录，即：sqlplus system/manager@topprd

# 查询表空间使用状况: 如果发现某个T100表空间（如dsdata或TEMP）没有在列表中出现，则说明该表空间已经100%被使用
SQL> @q_tbsFREE

# 查询各个数据文件信息
SQL> @q_datafile
SQL> @q_tbs

# 查询当前被锁定的表
SQL> @q_locktable
# 解除对某个表的锁定
SQL> alter system kill session 'SID,SERIAL' immediate;
# 查询当前系统中的锁
SQL> @q_lock
# 查询引起锁的SQL语句
SQL> @q_lockSQL

 ORACLE 用户管理

# SYS 系统管理员用户，默认密码:sys,只能以sysdba身份登录(Oracle限制),拥有最高权限
sqlplus sys/tiptop100@topprd as sysdba

# SYSTEM 管理员用户，默认密码：manager
sqlplus system/tiptop100@topprd

# T100新增的ORACLE用户
# ds 即我们通常所说的基础资料库，其中存放T100运行使用的基本业务数据和公用的系统数据
# dsdata 即我们通常使用的各个企业编号，其中存放的一般是各个企业编号中真实的业务数据（专用做测试的除外），系统数据均使用同义词（synonym）的方式从DS中存取
# dsrept、dsaps等，T100内部用户，不存放业务数据，我们在应用中不会直接使用

# Oracle用户相关的操作
# 创建用户（用户：test,密码：ds）
SQL> CREATE USER test IDENTIFIED BY ds DEFAULT TABLESPACE dsdata TEMPORARY TABLESPACE temp;
# 为用户赋权限
SQL> GRANT CONNECT,RESOURCE TO test;
# 更改用户密码
SQL> ALTER USER test IDENTIFIED BY test;
# 删除用户
SQL> DROP USER test CASCADE;

# 查询用户（账套）清单：T100用户全部位于dsdata表空间中
SQL> @q_users
# 查询用户所属对象的统计信息
SQL> @q_userOBJ

#查询T100有多少人在使用：azzq080

ORACLE表的相关操作

# 创建表,T100中标准的数据类型有：NUMBER,CHAR,DATA,VARCHAR2,BLOB
SQL> CREATE TABLE imx_T(
    imx01 VARCHAR2(60),
    imx02 VARCHAR2(10),
    imx03 SMALLINT,
    imx04 DATA,
    imx05 NUMBER(6)
);    

# 查询表结构
SQL> desc imx_T

# 使用现有数据来创建表
SQL> CREATE TABLE ... AS SELECT
# 复制一个表
SQL> CREATE TABLE imx_temp AS SELECT * FROM imx_T
# 创建一个空表
SQL> CREATE TABLE imx_temp AS SELECT * FROM imx_T WHERE 1=0
# 从几个表中选取记录来创建
SQL> CREATE TABLE imx_temp AS SELECT imx01,imd02 FROM imx_T,imd_T WHERE imx01=imd01
# 删除表
SQL> DROP TABLE imx_temp;
# 截断表: 截断表的结果是将表中数据全部删除，和使用DELETE语句的效果相同，两者的区别在与截断操作不记录Redo Log信息，所以更快，但操作无法恢复
# 所以在程序撰写过程中，一般对于临时表使用截断语句
SQL> TRUNCATE TABLE imx_temp;
# 修改表名称：修改表名之后原索引失效，必须将其重建
SQL> RENAME imx_temp to imx_temp1;
# 增加栏位
SQL> ALTER TABLE imx_T add imx11 varchar2(10);
# 删除栏位
SQL> ALTER TABLE imx_T drop column imx11;
# 修改栏位数据类型
SQL> ALTER TABLE imx_T modify imx11 varchar2(20);
# 栏位更名
SQL> ALTER TABLE imx_T rename column imx1 to imx2;
# 修改栏位NOT NULL约束
SQL> ALTER TABLE imx_temp modiy imx11 null/not null;

# 权限赋予
SQL> grant tiptop to dsdemo;
SQL> grant select on scott.dept to hr;
SQL> alter user dsdemo profile tiptop;
SQL> GRANT CREATE ANY TABLE TO dsdemo;
SQL> GRANT DROP ANY TABLE TO dsdemo;
SQL> GRANT CREATE PROCEDURE TO dsdemo;
SQL> GRANT UNLIMITED TABLESPACE TO dsdemo;

 Oracle同义词的操作(类似于快捷键)

# 创建同义词：CREATE SYNONYM [table] FOR [owner].[table];
SQL> CREATE SYNONYM test.gzxa_t FOR ds.gzxa_t;
# 删除同义词
SQL> DROP SYNONYM ze_T;

# 查询当前用户中的同义词信息
SQL> @q_synonym
# 查询数据库中所有的同义词
SQL> @q_syn

ORACLE 索引操作

# 创建索引：CREATE INDEX [name] ON [table]([fields,...])
SQL> CREATE INDEX test ON imx_T(imx01,imx02);

# 创建唯一索引
SQL> CREATE UNIQUE INDEX [name] ON [table]([fields,...])

# 删除索此
SQL> DROP INDEX imx_01;

# 重建索引：对于经常发生更新的表，因为被删除的行对应的索引信息不会自动释放，所以往往会造成索引碎片的现象，这时可以使用上面的语句对索引进行重建或整理     
SQL> ALTER INDEX test rebuild;

# 查询某个表的索引信息
SQL> @q_index
请输入欲查询之表格名称：imx_T

ORACLE导出导入工具之exp/imp

# exp/imp的三种模式
# 1、用户模式（U方式）：导出用户所有对象以及对象中的数据，备份多个企业编号
# 2、表模式（T方式）：导出用户所有表或者指定的表，备份一个表
# 3、整个数据库（Full方式）：导出数据库中所有对象，备份整个数据库

# 备份几个用户
SQL> exp system/tiptop100@topprd owner=dsdata,dsdemo,ds file=/u2/backup/backup.dmp log=/u2/backup/backup.log
# 备份用户下的表
SQL> exp ds/ds@topprd tables=gzxa_t,zxy_T file=backup.dmp
# 备份整个数据库
SQL> exp system/tiptop100@topprd full=y file=full.dmp

# imp,导入操作不支持覆盖，不支持增量，恢复前需要确认对象不存在
# 把指定用户数据导入另一个用户下
SQL> imp system/tiptop100@topprd fromUser=dsdemo toUser=dsdemo file=/u2/backup/dsdemo.dmp

# 还原某个表的数据：还原整个数据库，需要配合整个数据库实例重建才能使用
SQL> imp ds/ds@topprd tables=gzxa_t file=ds.dmp

ORACLE创建视图并授查看权限

# 查看ORACLE版本
sqlplus -v
# 登录数据库
sqlplus sys/tiptop100@topprd as sysdba
# 创建只读视图
CREATE OR REPLACE VIEW PRODUCT_VIEW AS
 SELECT * FROM PRODUCT_T
 WITH READ ONLY
# 创建用户
CREATE USER username IDENTIFIED BY password;
# 修改密码
ALTER USER username IDENTIFIED BY password;
# 删除用户
DROP USER username;
# 连接数据库权限   
GRANT CONNECT TO username;
# 赋予该用户登录数据库的权限.
grant create session to [username];
# 查询视图权限,在dsdemo登号创建，不用DSDEMO.
GRANT SELECT ON DSDEMO.PRODUCT_VIEW TO username;
# 撤销就使用revoke
revoke select on [view] to [username];
# 查询某个user拥有哪些系统权限
select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';
# 赋予用户创建视图的权限,需以dba用户登录：sqlplus / as sysdba，创建完再连接：conn usnamer/password
grant create view to [username];