linux上操作Oracle

自己这段时间在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.ora

    DZHBDB.__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;

  • 1
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值