Oracle 常用

一时失言乱红尘 2022-04-11 21:16 310阅读 0赞

忘记Oracle SYSTEM密码：  
  
1,打开Command命令提示符黑窗口 (win+R > cmd)  
  
2,启动Oracle相关服务（服务名可能不一样，若服务已启动可直接跳过）

net start OracleOraDb11g_home1TNSListener

net start OracleServiceORCL

3,进入SQLPLUS

sqlplus/nolog

4,连接到SYSDBA

conn / as sysdba

5,修改SYSTEM密码

alter user system identified by systempassword

6,解锁SYSTEM账户

alter user system account unlock

7,登录连接到SYSTEM

conn system/systempassword

Oracle建表空间建用户：

/***** 共四步 *****/
    /*第1步：创建临时表空间  */
    create temporary tablespace user_temp  
    tempfile 'D:\app\Administrator\oradata\orcl\user_temp.dbf' 
    size 100m  
    autoextend on  
    next 50m maxsize 20480m  
    extent management local;  
    
    /*第2步：创建数据表空间  */
    create tablespace user_data  
    logging  
    datafile 'D:\app\Administrator\oradata\orcl\user_data.dbf' 
    size 100m  
    autoextend on  
    next 50m maxsize 20480m  
    extent management local;  
    
    /*第3步：创建用户并指定表空间  */
    create user myusername identified by mypassword  
    default tablespace user_data  
    temporary tablespace user_temp;  
    
    /*第4步：给用户授予权限  */
    grant connect,resource,dba to username;

Oracle Kill Session

select Distinct 'alter system kill session ' || chr(39) || b.sid || ',' ||
                    b.serial# || chr(39) || ';' As CopyTheSQLToKillSession,
                    b.username,
                    b.logon_time
      from v$locked_object a, v$session b
     where a.session_id = b.sid
     order by b.logon_time;

Oracle 查看内存占用情况

SELECT trunc(sum(b.value) / 1024, 2) || 'K' 占用内存, A.PROGRAM 映像名称
      FROM V$SESSION A
      JOIN (SELECT B.SID, SUM(B.value) AS VALUE, C.name
              FROM v$sesstat B, v$sysstat C
             WHERE B.statistic# = C.statistic#
               and C.name in ('session pga memory') --,'session uga memory')
             GROUP BY B.SID, C.name) b
        ON A.SID = B.SID
     group by a.program
     order by sum(b.value) desc;

Oracle 查看字符集

--客户端字符集环境
    
    SELECT * FROM V$NLS_PARAMETERS;
    
    SELECT USERENV('language') FROM DUAL;
    
    --数据库服务器字符集
    
    SELECT * FROM NLS_DATABASE_PARAMETERS;
    
    --会话字符集环境
    
    SELECT * FROM NLS_SESSION_PARAMETERS;

Oracle 备份（导入导出）

exp 用户名/密码@远程的IP:端口/实例 file=存放的位置