【Oracle】第一次实验报告
追加日志文件
组:
SQL> alter database add logfile group 10(‘c:\1.log’,‘c:\2.log’)size 10m;
Database altered
成员:
SQL> alter database add logfile member’c:\3.log’ to group 10;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 26 52428800 1 NO CURRENT 1176728 2021/10/8 1
2 1 24 52428800 1 NO INACTIVE 1134949 2020/12/3 1
3 1 25 52428800 1 NO INACTIVE 1156482 2021/7/6 7:
10 1 0 10485760 3 YES UNUSED 0
删除重做日志文件组:
SQL> alter database drop logfile member’c:\3.log’;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 26 52428800 1 NO CURRENT 1176728 2021/10/8 1
2 1 24 52428800 1 NO INACTIVE 1134949 2020/12/3 1
3 1 25 52428800 1 NO INACTIVE 1156482 2021/7/6 7:
10 1 0 10485760 2 YES UNUSED 0
创建表空间:
SQL> create tablespace ts1 datafile ‘c:\1.dbf’ size 2m;
Tablespace created
数据文件一拖二:
SQL> create tablespace ts2 datafile ‘c:\2.dbf’ size 2m,‘c:\3.dbf’ size 5m;
Tablespace created
数据文件加上之后立刻删:
SQL> drop tablespace ts2 including contents and datafiles;
Tablespace dropped
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 3 SYSAUX 272629760 33280 AVAILABLE 3 YES 3435972198 4194302 1280 272564224 33272 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 2 UNDOTBS1 73400320 8960 AVAILABLE 2 YES 3435972198 4194302 640 73334784 8952 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 503316480 61440 AVAILABLE 1 YES 3435972198 4194302 1280 503250944 61432 SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 104792064 12792 ONLINE
C:\1.DBF 6 TS1 2097152 256 AVAILABLE 6 NO 0 0 0 2031616 248 ONLINE
6 rows selected
创建永久性表空间:
为案例数据库创建一个永久性的表空间,区自动扩展,段采用自动管理方式
SQL> create tablespace tb1 datafile’c:\td1.dbf’ size 10m;
Tablespace created
为案例数据库创建一个永久性的表空间,区定制分配,段采用自动管理的方式
SQL> create tablespace tb2 datafile’c:\td2.dbf’ size 50m extent management local uniform size 512k;
Tablespace created
为案例数据库创建一个永久性的表空间,区自动扩展,段采用手动管理的方式
SQL> create tablespace tb3 datafile’c:\td3.dbf’ size 50m segment space management manual;
Tablespace created
为案例数据库创建一个永久性的表空间,区定制分配,段采用手动管理的方式
SQL> create tablespace tb4 datafile’c:\td4.dbf’ size 50m extent management local uniform size 512k segment space management manual;
Tablespace created
为案例数据库创建一个永久性的表空间indx,段采用自动管理的方式,专门用于存储数据库中的索引数据
SQL> create tablespace indx datafile ‘c:\index1.dbf’ size 50m;
Tablespace created
创建大文件数据表空间:
在案例数据库文件中创建一个大文件表空间,文件大小为1GB,区的分配采用定制方式
SQL> create bigfile tablespace big_tbs datafile ‘c:\big01.dbf’ size 1G uniform size 512k;
Tablespace created
创建临时表空间
为案例数据库创建一个临时表空间hrtemp1
SQL> create temporary tablespace hrtemp1 tempfile ‘c:\hrtemp1.dbf’ size 20m extemt management local uniform size 15m;
Tablespace created