表空間
- 建用戶時指定配額
SQL> create user java identified by oracle default tablespace users temporary tablespace temp quota 100m on users quota 100m on system;
--注意temp表空間不支持表空間配額
2)更改用戶表空間配額
grant unlimited tablespace to java;--不對用戶作配額限制
alter user java quota unlimited on users;--對指定表空間不做限額
alter user java quota 1000m on users;--指定用戶在某表空間限制
revoke unlimited tablespace from java;--收回用戶的unlimited tablespace權(quán)限
alter user java quota 0 on users;--針對alter … quota unlimited …;
--如果用戶已經(jīng)被授予了unlimited tablespace權(quán)限的話，那么alter user java quota 0 on users;限制是不起作用的
創(chuàng)建DBLink
| 類型 | Owner | 描述 |
|---|---|---|
| Pr ivate | 創(chuàng)建database link的user擁有該database link | 在本地數(shù)據(jù)庫的特定的schema下建立的database link。只有建立該database link的schema的session能使用這個database link來訪問遠程的數(shù)據(jù)庫。同時也只有Owner能刪除它自己的private database link。 |
| Public | Owner是PUBLIC. | Public的database link是數(shù)據(jù)庫級的，本地數(shù)據(jù)庫中所有的擁有數(shù)據(jù)庫訪問權(quán)限的用戶或pl/sql程序都能使用此database link來訪問相應的遠程數(shù)據(jù)庫。 |
| Global | Owner是PUBLIC. | Global的database link是網(wǎng)絡級的，When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server. |
-- 創(chuàng)建
create database link dblink名稱 connect to 遠程用戶名 identified by "密碼" using 'ip:端口/sid';
-- 創(chuàng)建后使用
select * from abc@dblink名稱
調(diào)整表空間文件大小
/// 獲取指定表空間和位置
SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSTEM';
/// 獲取上一步需要調(diào)整的表空間文件的實際大小
SELECT MAX(BLOCK_ID)*8/1024 FROM DBA_EXTENTS WHERE FILE_ID=1;
/// 調(diào)整到上一步查詢到的實際大小，只能大于不能小魚
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' RESIZE 4000M;
