Oracle的功能性sql-有解無憂

--創建表空間

CREATE TABLESPACE FSNEW 
DATAFILE '/path/to/storeDataDir' SIZE 30G
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1M;

--查看當前用戶所有表

select * from user_tables;

--查看所有用戶及其表空間

select   username,default_tablespace from   dba_users ;
select * from all_users;

--洗掉用戶及其所有配置

drop user username CASCADE;

--修改用戶的表空間

alter user username default tablespace tablespacename;

--洗掉表空間

drop tablespace tablespacename including contents and datafiles cascade constraint;

--修改表空間狀態

alter tablespace tablespacename online;
select * from dba_tablespaces;
select name from v$datafile;
alter tablespace tablespacename rename datafile '/old/path/to/dataStoreDir' to '/new/path/to/dataStoreDir';
alter database rename file '/new/path/to/dataStoreDir/SYSTEM01.DBF' to '/old/path/to/dataStoreDir/SYSTEM01.DBF';

--創建用戶并賦權

CREATE USER case  IDENTIFIED BY case DEFAULT TABLESPACE XXX;

GRANT RESOURCE,DBA,CONNECT TO case ;
 
grant alter any table to case with admin option;
grant create session to case with admin option;
grant delete any table to case with admin option;
grant select any table to case with admin option;
grant unlimited tablespace to case with admin option;
grant update any table to case with admin option;

--同義詞

select 'create or replace  synonym ' || synonym_name || ' for dbname.' || table_name || ';'
  from user_synonyms;
  
--create or replace  synonym tablename for dbname.tablename;

--查看版本號

select banner||':'||(select utl_inaddr.get_host_name() from dual) version from v$version where rownum=1

--解決鎖表

SELECT l.session_id sid, 
   s.serial#
FROM v$locked_object l, all_objects o, v$session s 
WHERE l.object_id = o.object_id 
   AND l.session_id = s.sid 
ORDER BY sid, s.serial# ;
alter system kill session '128,3778'; 
alter system kill session '153,2841';

--命令匯出資料庫

--使用exp

exp username/password@ip/oanet file=D:\export\gd_base.dmp log=/path/to/exportlog/xxx.log full=y

--使用expdp匯出資料

1. 準備作業

連接目標資料庫，查看服務器端字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SQL>

2. 退出當前會話，設定客戶端字符集使之與服務端字符集一致

SQL> exit

從 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開

C:\>SET NLS_LANG= SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3. 創建邏輯目錄，并賦予Oracle對其的讀寫權限

使用EXPDP工具時，其轉存盤檔案只能被存放在directory物件對應的OS目錄中，而不能直接指定轉存盤檔案所在的OS目錄，在此，先在作業系統創建目錄C:\dump

以system等管理員身份登錄sqlplus，授予用戶test對目錄物件dmp_dir的讀寫權限，

create directory dmp_dir as 'C:\dump'

grant read, write on directory dmp_dir to hlsbi;

　　創建路徑需要sys權限，需要有create any directory權限才可以創建路徑，

　　選項：DIRECTORY=directory_object

Directory_object用于指定目錄物件名稱，需要注意，目錄物件是使用CREATE DIRECTORY陳述句建立的物件，而不是OS目錄，

--命令匯入資料庫

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36948.html

標籤：Oracle

上一篇：Oracle 兩個逗號分割的字串，獲取交集、差集的sql實作程序決議

下一篇：linux服務器下oracle開機自啟動設定