====== 启动与停止Oracle ======

====== Oracle配置 ======

====== Oracle备份与还原 ======

===== 客户端备份与还原：使用exp与imp进行备份与还原 =====

使用imp和exp前，需要先执行下面的命令设置编码格式

<code bash>
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
</code>
<code bash>
exp <user_id>/<password> file=<file_path>

#如果目标用户的默认表空间与表的默认表空间不同，imp前需要手动修改dmp文件中的表空间
#imp前先确认该用户的所有表、约束、触发器、存储过程、视图、序列等都已经删除
imp <user_id>/<password> fromuser=<origin_username> touser=<target_username> file=<dmp_file_path>
</code>
===== sqlplus执行sql脚本 =====

打开sqlplus，连接后执行''%%@<file_name_with_file_path>%%''

<code bash>
sqlplus /nolog
# 最好在sqlplus中先执行spool <filename>以将输出重定向到文件，执行完sql后执行spool off关闭重定向
sql>@<file_name_with_path>
</code>
===== 服务器端备份与还原：使用expdp和impdp进行备份与还原 =====

参考：[[https://blog.51cto.com/shitou118/310033|expdp impdp 数据库导入导出命令详解-石头博客-51CTO博客]]

<code bash>
# 导出
expdp <username>/<password>@<host>:<port>/<sid> schemas=<username> dumpfile=expdp.dmp DIRECTORY=DATA_PUMP_DIR;
# 导入
impdp <username>/<password>@<host>:<port>/<sid> DIRECTORY=DATA_PUMP_DIR dumpfile=expdp.dmp schemas=<username>;
</code>
====== Oracle用户管理 ======

===== 新建用户 =====

参考[[https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/|How to Create a User and Grant Permissions in Oracle]]

<code sql>
create user <user_name> identified by <password> default tablespace <tablespace_name>; --创建新用户，并指定默认表空间（可以不指定），password含有@符号时会报错，如果希望包含@符号，不清楚如何实现
grant connect, resource, dba to <user_name>; --为用户授予权限
</code>
===== 删除用户 =====

<code sql>
drop user <user_name>; --禁止删除sys用户和system用户
</code>
====== Oracle表空间管理 ======

===== 新建表空间 =====

<code sql>
create tablespace <tablespace_name> datafile '<file_path>' size <size_in_m_or_g> autoextend on; --创建自动扩展的表空间
</code>
===== 修改表的表空间 =====

详见：[[https://stackoverflow.com/questions/18327671/how-to-move-table-from-one-tablespace-to-another-in-oracle-11g|oracle11g - How to move table from one tablespace to another in oracle 11g - Stack Overflow]]

<code sql>
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>; --移动到新的表空间
alter index <owner>."<index_name>" rebuild; --移动表空间会导致索引失效，因此需要重建索引
</code>
====== 命令行重启Oracle ======

<code bash>
su oracle --登录 
sqlplus /nolog --进入SQLPLUS
</code>
<code sql>
connect / as sysdba --切换管理员登录
shutdown --关闭
startup  --启动
</code>
====== 数据库对象解锁 ======

<code sql>
--数据库对象解锁
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;

SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (
    SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = '<Object_Name>'
);
ALTER SYSTEM KILL SESSION '<session>, <session>';
</code>
====== 删除表、视图、序列等 ======

<code sql>
BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
</code>
====== 解决数据库系统序列值比最大id小导致无法插入数据的问题 ======

<code sql>
--解决数据库系统序列值比最大id小导致无法插入数据的问题
declare
   v_rlt number(8):=1;
 begin
    <<fst_loop>>
    LOOP
      SELECT  "TQYDZY"."ISEQ$$_92176".nextval INTO v_rlt FROM dual;
      dbms_output.put_line('v_rlt = '||v_rlt);
      exit fst_loop when v_rlt > 5242;--序列的期望值
    end loop;
      dbms_output.put_line('LOOP循环已经结束！');
 end;
</code>
====== 常见Oracle管理问题 ======

===== 没有任何数据的表格不能导出的解决办法 =====

自Oracle11g R2开始，插入第一条数据才分配空间，所以没有数据的表默认没有分配空间，使用exp命令无法导出这些表。

解决办法是执行下面的sql得到修改表的空间分配方案的sql命令，再执行这些sql（[[https://confluence.atlassian.com/kb/exporting-a-database-does-not-export-some-tables-744326156.html|点击查看更多信息]]）：

<code sql>
BEGIN
    FOR i IN (SELECT table_name FROM user_tables WHERE segment_created = 'NO') LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' ALLOCATE EXTENT ';
    END LOOP;
END;
</code>