Differences

This shows you the differences between two versions of the page.

--- 管理oracle [2017/12/30 02:20]
jordan
+++ 管理oracle [2023/03/08 16:09] (current)
xujianglong ↷ Page moved from 内部资料:管理oracle to 管理oracle
@@ Line 1: / Line 1: @@
-# 启动与停止Oracle
+====== 启动与停止Oracle ======
-# Oracle配置
+====== Oracle配置 ======
-# Oracle备份与还原
+====== Oracle备份与还原 ======
-# Oracle用户管理
+===== 客户端备份与还原：使用exp与imp进行备份与还原 =====
-## 新建用户
+使用imp和exp前，需要先执行下面的命令设置编码格式
-参考[How to Create a User and Grant Permissions in Oracle](https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/)
+<code bash>
+export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
+</code>
+<code bash>
+exp <user_id>/<password> file=<file_path>
-```sql
+#如果目标用户的默认表空间与表的默认表空间不同，imp前需要手动修改dmp文件中的表空间
-create user <user_name> identified by <password>; --password含有@符号时会报错，如果希望包含@符号，不清楚如何实现
+#imp前先确认该用户的所有表、约束、触发器、存储过程、视图、序列等都已经删除
-grant connect, resource, dba to <user_name>; --为用户授予权限
+imp <user_id>/<password> fromuser=<origin_username> touser=<target_username> file=<dmp_file_path>
-```
+</code>
+===== sqlplus执行sql脚本 =====
-## 删除用户
+打开sqlplus，连接后执行''%%@<file_name_with_file_path>%%''
-```sql
+<code bash>
-drop user <user_name>; --禁止删除sys用户和system用户
+sqlplus /nolog
-```
+# 最好在sqlplus中先执行spool <filename>以将输出重定向到文件，执行完sql后执行spool off关闭重定向
+sql>@<file_name_with_path>
+</code>
+===== 服务器端备份与还原：使用expdp和impdp进行备份与还原 =====
-# Oracle表空间管理
+参考：[[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用户管理 ======
-```sql
+===== 新建用户 =====
+参考[[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>
-# 常见Oracle管理问题

知识库｜湖南麓川信息科技有限公司

User Tools

Site Tools

Differences

Page Tools