oracle資料庫簡單操作

標籤：oda   read   mini   odata   目錄   服務   res   作業系統   dmi   

匯入某使用者所有表和資料：
imp sgp/[email protected]:1521/orcl file=sgp20161025.dmp full=y

匯出指定表及資料：
exp sgp/[email protected]:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

匯入指定表及資料：
imp sgp/[email protected]:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

 

Linux環境下（已安裝好oracle資料庫和用戶端）：

#登陸linux伺服器，切換到oracle使用者下
su - oracle

#不在cmd或者terminal當中暴露密碼的登陸方式
sqlplus /nolog

#以作業系統許可權認證的oracle sys管理員登陸
SQL>connect sys/[email protected] as sysdba

#建立資料表空間（ASM模式）
SQL>
create tablespace geodata(資料表空間名) datafile ‘+data‘ size 500m AUTOEXTEND ON next 64m maxsize unlimited;
Tablespace created.

#建立使用者，並指定資料表空間
SQL> create user geouser(使用者名稱) identified by geouseradmin(密碼) default tablespace geodata(資料表空間名);
User created.

#給使用者授予許可權
SQL> grant connect,resource,dba(Oracle最大許可權) to geouser(使用者名稱);
Grant succeeded.

ALTER USER username ACCOUNT UNLOCK;

擷取表的列資訊
select * from user_tab_columns where table_name = ‘CMS_T_VIEW_RECORD‘

#查看目前使用者的預設資料表空間
SQL>select username,default_tablespace from user_users;

#查看已建好的資料表空間
SQL> select file_name,tablespace_name from dba_data_files;

#查看目前使用者的資料表空間和有哪些表
SQL> select table_name,tablespace_name from user_tables;

#查看某個資料表空間下有哪些表
SQL> select * from all_tables where tablespace_name=‘sde‘;

#資料表空間的資料檔案屬性改為自動擴充
SQL> alter tablespace system autoextend on;

#查看當前串連的資料庫IP地址
SQL> select utl_inaddr.get_host_address from dual;

#查看當前資料庫
SQL> select name from V$DATABASE;

#查看資料庫執行個體名稱
select instance_name from v$instance;

#查看錶結構
SQL> describe oper_data(表名);

/*** 查看錶中資料 ***/
SQL> select * from oper_data;

/*** 查看當前登入的使用者名稱 ***/
SQL> select user from dual;

/*** 查看目前使用者擁有的角色和許可權 ***/
SQL> select * from user_role_privs;
SQL> select * from session_privs;

/*** 查看所有的預設資料表空間 ***/
select tablespace_name from dba_tablespaces;

/*** 查看指定使用者的預設資料表空間 ***/
select default_tablespace.username from dba_users;

/*** 重新命名資料表空間名稱 ***/
alter tablespace oldname rename to newname;

/*** 修改資料表空間的讀寫狀態 ***/
alter tablespace tablespace_name read {only|write};

/*** 設定資料表空間的可用狀態 ***/
alter tablespace tablespace_name { online|offline[normal|temporary|immediate];

/*** 建立大檔案資料表空間 ***/
CREATE BIGFILE TABLESPACE tablespacename DATAFILE filename SIZE size;

/*** 刪除資料表空間 ***/
drop tablespace tablespace_name [include contents] [cascade constrain
ts];

/***************************************

su - oracle
sqlplus /nolog
SQL> connect geouser/geouseradmin

PROFILE是Oracle中的概要檔案，在PROFILE中主要存放的就是資料庫中的系統資源或者
資料庫使用限制的一些內容。

1.查看所有使用者名稱：
select username from dba_users;
select * from all_users;
select * from user_users;

2.查看使用者或角色系統許可權(直接賦值給使用者或角色的系統許可權)：
select * from dba_sys_privs;
select * from user_sys_privs;

3.查看角色(只能查看登陸使用者擁有的角色)所包含的許可權
sql>select * from role_sys_privs;

4.查看使用者物件許可權：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs; //返回目前使用者所有的對象許可權

5.查看所有角色：
select * from dba_roles;

6.查看使用者或角色所擁有的角色：
select * from dba_role_privs;
select * from user_role_privs;

7.查看哪些使用者有sysdba或sysoper系統許可權(查詢時需要相應許可權)
select * from V$PWFILE_USERS

8、把某個表mytab的所有許可權都賦予給使用者test：
grant all on mytab to test；
撤銷所有許可權
revoke all on mytab to test；

9、對象許可權可以控制到列（查詢和刪除不能控制到列）
grant update(name) on mytab to test;
grant insert(id) on mytab to test;

10、刪除審計表
TRUNCATE TABLE SYS.AUD$;

window環境DOS下：

給使用者sys增加匯入匯出資料的許可權：
grant EXP_FULL_DATABASE, IMP_FULL_DATABASE TO SDE;

匯出操作
1、將資料庫使用者名稱sys 密碼 *** 使用者空間所有表和資料匯出
exp username/userpassword file=份檔案路徑及檔案名稱 owner=username

2、將資料庫中sde使用者的表匯出
exp \"sys/[email protected] as sysdba\" file=f:\備份\export.dump owner=sde

3、將資料庫中的表 SYS.AUD$ 匯出
exp \"sys/[email protected] as sysdba\" file=f:\備份\export.dump tables=(table1,table2)

4、匯出審計表
exp nmgqyrk/[email protected] file=exp.dmp tables=SYS.AUD$

匯入操作：
1、將f:\備份\export.dump中的資料匯入到NMGDB2中
imp \"sys/[email protected] as sysdba\" file=f:\備份\export.dump

2、將f:\備份\export.dump中的表tableA匯入（先刪除原表）
imp \"sys/[email protected] as sysdba\" file=f:\備份\export.dump tables=(table1,table2)

window環境下（已經安裝好oracle資料庫和用戶端）：

1：首先運行SQLPLUS

C:\Documents and Settings\Administrator>sqlplus /nolog（運行cmd後可直接運行SQLPLUS）

SQL*Plus: Release 10.1.0.2.0 - Production on 星期一 9月 3 15:15:27 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

2：以sysdba的許可權串連資料庫

SQL> connect /as sysdba;

3：建立資料表空間

SQL> create tablespace cms datafile ‘G:\Oracle\Administrator\oradata\orcl\sgp.dbf‘ size 256m uniform size 128k;

資料表空間名為cms，資料庫檔案目錄D:\oracle\10.1.0\oradata\orcl\cms.dbf

4：把資料表空間改為自動擴充儲存空間大小

SQL> alter database datafile ‘G:\Oracle\Administrator\oradata\orcl\sgp.dbf‘ autoextend on;

5：建立使用者

SQL> create user cms identified by cms default tablespace cms;

註：使用者名稱cms 密碼cms 預設資料表空間cms;

6：為使用者授權

SQL> grant connect ,resource,dba to cms;

把串連管理以dba的許可權授權給cms;

1.查看使用者與使用者對應的標空間
select username,default_tablespace from dba_users;

2.查看錶空間與資料檔案的對應關係
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;

該資料庫匯出檔案的編碼類別型為GBK，可能會與匯入的資料庫編碼類別型不一致，從而產生亂碼。

亂碼解決辦法如下：
修改資料庫字元集為：ZHS16GBK
查看伺服器端字元集
SQL > select * from V$NLS_PARAMETERS; NLS_CHARACTERSET ZHS16GBK
修改：
$sqlplus /nolog
SQL>conn /as sysdba
若此時資料庫伺服器已啟動，則先執行 SHUTDOWN IMMEDIATE 命令關閉資料庫伺服器，然後執行以下命令:
資料庫關閉： SQL>shutdown immediate;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE character set INTERNAL_USE zhs16gbk;
資料庫啟動：SQL> startup

11g oracle匯出表時會預設不匯出資料為空白
1、Oracle11g預設對空表不分配segment，故使用exp匯出Oracle11g資料庫時，空表不會匯出。
2、設定deferred_segment_creation 參數為FALSE後，無論是空表還是非空表，都分配segment。
SQL>show parameter deferred_segment_creation;
SQL>alter system set deferred_segment_creation=false;

/**************************************************
擷取目前時間
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual

匯出表和資料（包括空表）方法：
SQL> connect sgp/sgp
SQL> set echo off
SQL> set feedback off
SQL> set pagesize 0
SQL> set linesize 9000
SQL> set tab off
SQL> spool sgp_deal_tablenull_107.sql
SQL> select ‘alter table ‘||table_name||‘ allocate extent;‘ from user_tables where num_rows=0;
SQL> spool off

執行完以後，你會得到一個檔案：sgp_deal_tablenull_107.sql，
把這個檔案裡面的非sql語句刪掉，只保留alter table的那些sql，
然後執行一下這些sql
執行完後再匯出：exp sgp/sgp file=檔案名稱 owner=sgp

匯入資料（不附帶建立表和資料表空間限制,前提條件是表名稱一致）
imp scm/[email protected]:1521/orcl file=sgp99dept.dmp tables=scm_dept ignore=y

--將以下sql語句查詢結果匯出，即可隨時進行注釋的恢複與刪除
--查詢(匯出)表注釋
select ‘comment on table ‘||t.table_name||‘ is ‘‘‘||t.comments||‘‘‘;‘ from user_tab_comments t;

--查詢(匯出)列注釋
select ‘comment on column ‘||c.table_name||‘.‘||c.column_name||‘ is ‘‘‘||c.comments||‘‘‘;‘ from user_col_comments c;
----------------------------------------------------------------------------------

--刪除表注釋
1、select ‘comment on table ‘||t.table_name||‘ is ‘‘‘‘;‘ from user_tab_comments t; 擷取表注釋結果
2、匯出到cvs檔案中，使用execl開啟，拷貝到plsql中執行即可刪除

--刪除列注釋
1、select ‘comment on column ‘||c.table_name||‘.‘||c.column_name||‘ is ‘‘‘‘;‘ from user_col_comments c; 擷取表注釋結果
2、匯出到cvs檔案中，使用execl開啟，拷貝到plsql中執行即可刪除

oracle資料庫簡單操作