Abfrage- und Betriebsmethoden für Oracle-Tabellenbereiche

1. Abfrage
1. Fragen Sie die Nutzung des Oracle-Tabellenbereichs ab
wählen Sie b.file_id Datei-ID,
b.tablespace_name Tabellenbereich,
b.file_name Physischer Dateiname,
b.bytes Gesamtzahl Anzahl Bytes,
(b.bytes-sum(nvl(a.bytes,0))) Verwendet,
sum(nvl(a.bytes,0)) Verbleibend,
sum(nvl( a. Bytes,0))/(b.bytes)*100 Verbleibender Prozentsatz
von dba_free_space a,dba_data_files b
wobei a.file_id=b.file_id
gruppiert nach b.tablespace_name,b.file_name, b. file_id,b.bytes
Sortieren nach b.tablespace_name
2. Fragen Sie den Standard-Tablespace und den temporären Tablespace von Oracle-Systembenutzern ab
wählen Sie default_tablespace,temporary_tablespace aus dba_users
3 Fragen Sie eine einzelne Tabellenverwendung ab
Wählen Sie segment_name,bytes aus dba_segments aus, wobei segment_name = 'RE_STDEVT_FACT_DAY' und Eigentümer = USER
RE_STDEVT_FACT_DAY der Name der Tabelle ist, die Sie abfragen möchten
4. Fragen Sie die 30 größten Nutzungsgrößen aller Benutzertabellen ab
wählen * from (select segment_name,bytes from dba_segments whereowner = USER order by bytes desc ) where rownum <= 30
5 Abfragen der Nutzung des Standardtabellenbereichs des aktuellen Benutzers
select tablespacename,sum(totalContent ), sum(usecontent),sum(sparecontent),avg(sparepercent)
from
(
SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes -sum (nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent
FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id und b.tablespace_name = (wählen Sie default_tablespace aus dba_users aus, wobei Benutzername = Benutzer)
gruppieren nach b.tablespace_name,b.file_name,b .file_id ,b.bytes
)
GROUP BY tablespacename
6. Fragen Sie die Tabellen des Benutzertabellenbereichs ab
wählen Sie * aus user_tables
============== ============================================== = =================
1. Tabellenbereich erstellen
TABLESPACE-Test erstellen
DATAFILE 'c:/oracle/oradata/db/test01.dbf ' SIZE 50M
UNIFORM SIZE 1M; #Die angegebene Bereichsgröße beträgt 128 KB. Wenn nicht angegeben, beträgt die Bereichsgröße standardmäßig 64 KB
oder
CREATE TABLESPACE test
DATAFILE 'c:/oracle/oradata/db / test01.dbf' GRÖSSE 50M
MINIMALE EXTENT 50K EXTENT MANAGEMENT LOCAL
STANDARDSPEICHER (ANFANG 50K NÄCHSTE 50K MAXEXTENTS 100 PCTINCREASE 0);
Sie können die Informationen des neu erstellten Tabellenbereichs von dba_tablespaces anzeigen
II, UNDO-Tabellenbereich erstellen
UNDO-TABLESPACE test_undo erstellen
DATENDATEI 'c:/oracle/oradata/db/test_undo.dbf' GRÖSSE 50M
Der Umfang des UNDO-Tabellenbereichs wird lokal und bei der Erstellung verwaltet In SQL-Anweisungen können nur DATAFILE- und EXTENT MANAGEMENT-Klauseln verwendet werden.
ORACLE legt fest, dass der Datenbank immer nur ein Wiederherstellungs-Tablespace zugewiesen werden kann, d. h. in einer Instanz können mehrere Wiederherstellungs-Tablespaces vorhanden sein, aber nur einer kann aktiv sein. Mit dem Befehl ALTER SYSTEM können Sie zu einem wiederhergestellten Tabellenbereich wechseln.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo; Den aktuellen Standard-Temporärtabellenbereich des Systems anzeigen
wählen Sie * aus dba_properties aus, wobei Eigenschaftsname wie „DEFAULT%“ lautet
Ändern Sie den Standard-Temporärtabellenbereich des Systems
Ändern Sie den Standard-Temporärtabellenbereich test_temp
4
1. Schalten Sie den Tabellenbereich offline.
ALTER TABLESPACE test OFFLINE; Verwenden Sie den Tabellenbereich online
ALTER TABLESPACE-Test ONLINE; schalten Sie die Datendatei offline
ALTER DATABASE DATAFILE 3 OFFLINE;
5. Machen Sie den Tabellenbereich schreibgeschützt
ALTER TABLESPACE test READ ONLY; space
DROP TABLESPACE test INCL ING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE table space name [INCL ING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]
1. INCL ING CONTENTS-Klausel wird zum Löschen von Segmenten verwendet
2. Die DATAFILES-Klausel wird verwendet, um Datendateien zu löschen.
3. Die CASCADE CONSTRAINTS-Klausel wird verwendet, um alle referenziellen Integritätsbeschränkungen zu löschen.

Erweiterter Tabellenbereich des Tabellenbereichs und der Datei, zu der er gehört
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
1. Datendatei hinzufügen
ALTER TABLESPACE test
ADD DATAFILE '/oracle/oradata/db/test02.dbf' SIZE 1000M;
2. Datendateigröße manuell erhöhen
ALTER DATABASE DATAFILE 'c: /oracle/oradata/db/test01.dbf'
RESIZE 100M
3. Stellen Sie die Datendatei so ein, dass sie automatisch erweitert wird
ALTER DATABASE DATAFILE 'c:/oracle/oradata/db/test01.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 200M;
Nach der Einstellung können Sie die Tabellenbereichsinformationen von dba_tablespace und die entsprechenden Datendateiinformationen von v$datafile anzeigen
========== =============================================== ======================
Tabellenbereich scgl erstellen
Datendatei 'E:ORACLEPROD T10.1.0ORADATAORCLscgl2.dbf'
Größe 50 m
Autoextend auf
nächste 50m maxsize 20480m
Extent Management Local; Autoextend on
Next 50m Maxsize 20480m
Extent Management Local;
Benutzer-Scgl erstellen, identifiziert durch qwer1234
Standard-Tablespace-Scgl
temporärer Tablespace scgl_temp;
tempfile 'E:ORACLEPROD T10.1.0ORADATAORCLuser_ temp .dbf'
temporären Tablespace scgl_temp erstellen
tempfile 'E:ORACLEPROD T10.1.0ORADATAORCLscgl_temp.dbf'
Größe 50m
autoextend on
next 50m maxsize 20480m
extent management local;grant connect,resource, dba to scgl;
oracle erstellt den Tabellenbereich SYS-Benutzer meldet sich als DBA unter CMD an:
Geben Sie sqlplus ein /nolog in CMD
und dann
conn / as sysdba
//Temporären Tablespace erstellen
temporären Tablespace user_temp erstellen
tempfile 'D:oracleoradataOracle9iuser_temp.dbf'
Größe 50 m
automatische Erweiterung auf
nächste 50 m, maximale Größe 20480 m
Extend Management Local ;
//Datentabellenbereich erstellen
Tabellenbereich Testdaten erstellen
Protokollierung
Datendatei 'D:oracleoradataOracle9iuser_data.dbf'
Größe 50 m
Automatische Erweiterung auf
nächste 50 m, maximale Größe 20480 m
Extent-Management lokal;
//Benutzer erstellen und Tabellenbereich angeben
Benutzernamen erstellen, der durch Passwort identifiziert wird
Standardtabellenbereich Benutzerdaten
Temporärer Tabellenbereich user_temp;
Tablespace-Nutzung abfragen
SELECT UPPER(F.TABLESPACE_NAME) „Tablespace-Name“,
D.TOT_GROOTTE_MB „Tablespace-Größe (M)“,
D.TOT_GROOTTE_MB – F.TOTAL_BYTES „Genutzter Speicherplatz (M)“,
TO_CHAR (ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990,99') ||. '%' „Nutzungsverhältnis“,
F.TOTAL_BYTES „freier Speicherplatz (M)“,
F.MAX_BYTES „Maximaler Block (M)“
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2 ) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
Fragen Sie den freien Speicherplatz im Tabellenbereich ab.
wählen Sie den Tabellenbereichsnamen aus (Blöcke) als Blöcke
von dba_free_space
gruppieren nach Tabellenbereichsname;
– Fragen Sie die Gesamtkapazität des Tabellenbereichs ab
wählen Sie Tabellenbereichsname, Summe (Bytes) / 1024 als MB
von dba_data_files
gruppieren nach Tabellenbereichsname;
Tabellenplatznutzung abfragen
wählen Sie total.tablespace_name,
round(total.MB, 2) als Total_MB,
round(total.MB - free.MB, 2). ) als Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
aus dba_free_space
gruppieren nach Tabellenbereichsname) frei,
(wählen Sie Tabellenbereichsnamen aus, Summe (Byte) / 1024/1024 als MB
aus dba_data_files
gruppieren nach Tabellenbereichsnamen) insgesamt
wo frei. tablespace_name = total.tablespace_name;
-- ------------------------ ----------- ------------------------- ---------- --------
1. Tablespace erstellen: Tablespace-Testdatendatei erstellen '/u01/ test.dbf' Größe 10M einheitliche Größe 128k
#Die angegebene Bereichsgröße beträgt 128 KB und die Blockgröße beträgt standardmäßig 8 KB
#Big File Tablespace BigFile Tablespace erstellen big_tbs Datafile '/u01/big_tbs.dbf ' Größe 100G
2. 建非标准表show parameter db alter system set db_2k_cache_size=10M create tablespace test datafile '/u01/test.dbf' size 10M blocksize 2K uniform size 128k 
#常见错误 
SQL> alter system set db_2k_cache_size=2M; alter system set db_2k_cache_size=2M FEHLER in Zeile 1: ORA-02097: Parameter kann nicht geändert werden, da der angegebene Wert ungültig ist ORA-00384: Nicht genügend Speicher zum Erweitern des Caches 
#解决 
SQL> alter system set sga_max_size=400M Scope=spfile; SQL> sofortige Abschaltung; SQL> Start-SQL> alter system set db_2k_cache_size=10M; System geändert. 
3.查看区大小与块大小#区大小 conn y / 123 create table t(i number) tablespace test; Einfügen in t-Werte(10) select bytes/1024 from user_segments where segment_name=upper('t'); 
#块大小 Parameterblock anzeigen (ca. 64 KB) 
#非标准表空间的blocksize SQL> wähle * aus v$dbfile; SQL> Wählen Sie Name, Blockgröße und Status aus v$datafile aus. SQL> Wählen Sie block_size aus v$datafile aus, wobei file#=14; 
4.删除表空间Drop-Tablespace-Test einschließlich Inhalten und Datendateien 
5.查表空间:#查数据文件 select * from v$dbfile; #所有表空间 select * from v$tablespace; 
#表空间的数据文件 select file_name,tablespace_name from dba_data_files; 
6.Undo表空间Undo-Tablespace erstellen undotbs01 Datendatei '/u01/undotbs01.dbf' Größe 5M; 
#切换到新建的undo表空间 alter system set undo_tablespace=undotbs01; 
7. 建立临时表空间Temporären Tabellenbereich temp_data tempfile '/u01/temp.db' Größe 5M erstellen; Erstellen Sie einen temporären Bigfile-Tablespace. Bigtem-Tempfile „/u01/bigtemp.db“ Größe 5 MB; 
8.改变表空间状态 
(0.)查看状态 
#表空间状态 select tablespace_name,block_size,status from dba_tablespaces; 
#数据文件状态 select name,block_size,status from v$datafile; 
(1.)表空间脱机Alter Tablespace Test offline .)数据文件脱机select * from v$dbfile; Datenbankdatendatei 3 offline ändern 
(4.)数据文件联机Datendatei 3 wiederherstellen; Datenbankdatendatei 3 online ändern; 
(5.)使表空间只读Alter Tablespace Test Read Only.
(6.)使表空间可读写Alter Tablespace Test Read Write; 
9.扩展表空间#首先查看表空间的名字和所属文件及空间 select tablespace_name, file_id, file_name,round(bytes/(1024),0) total_space from dba_data_files. order von tablespace_name; #三种扩展方法 
1.Tablespace-Test ändern, Datendatei „/u01/test02.dbf“ hinzufügen, Größe 10 M(自动加一个个Datendatei) 
2.Datenbankdatendatei „/u01/test.dbf“ ändern, Größe ändern 20M; 
3. Datenbankdatendatei „/u01/test.dbf“ ändern, automatisch auf die nächsten 10 MB erweitern, maximale Größe 1 GB; 
#设定后查看表空间信息 
select a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes*100)/a.bytes „% used“, (c.bytes*100)/a.bytes „% frei“ von sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c wobei a.tablespace_name=b.tablespace_name und a.tablespace_name=c .tablespace_name; 
10.移动表空间的数据文件 
#先确定数据文件据在表空间 
SQL>select tablespace_name,file_name from dba_data_files where file_name='/u01/ test.dbf'; 
#open状态 
SQL>alter tablespace test offline; SQL>Host-Verschiebung /u01/test.dbf /u01/oracle/test.dbf; SQL>Tablespace-Test ändern, Datendatei „/u01/test.dbf“ in „/u01/oracle/test.dbf“ umbenennen; SQL>Tablespace-Test offline ändern; 
#mount状态 SQL>shutdown strict; SQL>Startup-Mount SQL>Host-Verschiebung /u01/test.dbf /u01/oracle/test.dbf; SQL>Datenbank ändern, Datei „/u01/test.dbf“ in „/u01/oracle/test.dbf“ umbenennen; 
11 --查询表空间使用情况 
SELECT UPPER(F. ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990,99') || '%' "Menge", 
F.TOTAL_BYTES "Menge(M)", 
F.MAX_BYTES "Menge(M)" 
FROM (SELECT TABLESPACE_NAME, 
ROUND(SUM( BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
FROM SYS.DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F, 
(SELECT DD.TABLESPACE_NAME, 
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
FROM SYS.DBA_DATA_FILES DD 
GROUP BY DD.TABLESPACE_NAME) D 
WHERE D .TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY 1 
--查询表空间的freier Speicherplatz 
select tablespace_name, 
count(*) as extensions, 
round(sum(bytes) / 1024 / 1024, 2) als MB, 
Summe(Blöcke) als Blöcke 
von dba_free_space 
Gruppe nach Tabellenbereichsname; 
--查询表空间的总容量 
select tablespace_name, sum(bytes) / 1024/1024 as MB 
from dba_data_files 
group by tablespace_name; 
--查询表空间使用率 
select total.tablespace_name, 
round(total.MB, 2) as Total_MB, 
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' als Used_Pct 
from (select tablespace_name, sum(bytes) / 1024/1024 as MB 
from dba_free_space 
group by tablespace_name) free, 
(select tablespace_name, sum(bytes) / 1024 / 1024 als MB 
aus dba_data_files 
group by tablespace_name) insgesamt 
wobei free.tablespace_name = total.tablespace_name; 
//给用户授予权限 
Grant connect,ressource to username; 
//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间， 
这就不用在每创建一个对象给其指定表空间了 
撤权: 
权限... von 用户名 widerrufen; 
删除用户命令 
Drop user user_name cascade; 

建立表空间 
CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 

删除表空间 
DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES; 
一、建立表空间 
CREATE TABLESPACE data01 
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M 
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k 
二、建立UNDO表空间 
CREATE UNDO TABLESPACE UNDOTBS02 
DATAFILE '/oracle/ oradata/db/UNDOTBS02.dbf' GRÖSSE 50 MB
#注意：在OPEN状态下某些时刻只能用一个UNDO表空间, 如果要用新建的表空间, 必须切换到该表空间: 
ALTER SYSTEM SET undo_tablespace=UNDOTBS02; 
三、建立临时表空间 
CREATE TEMPORARY TABLESPACE temp_data 
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M 
四、改变表空间状态 
1.使表空间脱机 
ALTER TABLESPACE-Spiel OFFLINE; 
如果是意外删除了数据文件，则必须带有RECOVER选项 
ALTER TABLESPACE game OFFLINE FOR RECOVER; 
2.使表空间联机 
ALTER TABLESPACE-Spiel ONLINE; 
3.使数据文件脱机 
ALTER DATABASE DATAFILE 3 OFFLINE; 
4.使数据文件联机 
ALTER DATABASE DATAFILE 3 ONLINE; 
5.使表空间只读 
ALTER TABLESPACE game READ ONLY; 
6.使表空间可读写 
ALTER TABLESPACE game READ WRITE; 
五、删除表空间 
DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES; 

六、扩展表空间 
首先查看表空间的名字和所属文件 
select tablespace_name, file_id, file_name, 
round(bytes/(1024*10 24),0) total_space 
von dba_data_files 
Reihenfolge nach Tabellenbereichsname; 
1.增加数据文件 
ALTER TABLESPACE game 
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M; 
2.手动增加数据文件尺寸 
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' 
RESIZE 4000M; 
3.设定数据文件自动扩展 
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M; 

设定后查看表空间信息 
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, 
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" 
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C 
WHERE A. TABLESPACE_NAME=B.TABLESPACE_NAME UND A.TABLESPACE_NAME=C.TABLESPACE

更多Oracle >