-
oracle第八周实验--2023-10-27
oracle第八周实验--2023-10-27
SQL> select * from hr.employees;
- Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
- Connected as system@ORCL
- SQL> select * from hr.employees;
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 198 Donald OConnell DOCONNEL 650.507.9833 1999/6/21 SH_CLERK 2600.00 124 50
- 199 Douglas Grant DGRANT 650.507.9844 2000/1/13 SH_CLERK 2600.00 124 50
- 200 Jennifer Whalen JWHALEN 515.123.4444 1987/9/17 AD_ASST 4400.00 101 10
- 201 Michael Hartstein MHARTSTE 515.123.5555 1996/2/17 MK_MAN 13000.00 100 20
- 202 Pat Fay PFAY 603.123.6666 1997/8/17 MK_REP 6000.00 201 20
- 203 Susan Mavris SMAVRIS 515.123.7777 1994/6/7 HR_REP 6500.00 101 40
- 204 Hermann Baer HBAER 515.123.8888 1994/6/7 PR_REP 10000.00 101 70
- 205 Shelley Higgins SHIGGINS 515.123.8080 1994/6/7 AC_MGR 12000.00 101 110
- 206 William Gietz WGIETZ 515.123.8181 1994/6/7 AC_ACCOUNT 8300.00 205 110
- 100 Steven King SKING 515.123.4567 1987/6/17 AD_PRES 24000.00 90
- 101 Neena Kochhar NKOCHHAR 515.123.4568 1989/9/21 AD_VP 17000.00 100 90
- 102 Lex De Haan LDEHAAN 515.123.4569 1993/1/13 AD_VP 17000.00 100 90
- 103 Alexander Hunold AHUNOLD 590.423.4567 1990/1/3 IT_PROG 9000.00 102 60
- 104 Bruce Ernst BERNST 590.423.4568 1991/5/21 IT_PROG 6000.00 103 60
- 105 David Austin DAUSTIN 590.423.4569 1997/6/25 IT_PROG 4800.00 103 60
- 106 Valli Pataballa VPATABAL 590.423.4560 1998/2/5 IT_PROG 4800.00 103 60
- 107 Diana Lorentz DLORENTZ 590.423.5567 1999/2/7 IT_PROG 4200.00 103 60
- 108 Nancy Greenberg NGREENBE 515.124.4569 1994/8/17 FI_MGR 12000.00 101 100
- 109 Daniel Faviet DFAVIET 515.124.4169 1994/8/16 FI_ACCOUNT 9000.00 108 100
- 110 John Chen JCHEN 515.124.4269 1997/9/28 FI_ACCOUNT 8200.00 108 100
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 111 Ismael Sciarra ISCIARRA 515.124.4369 1997/9/30 FI_ACCOUNT 7700.00 108 100
- 112 Jose Manuel Urman JMURMAN 515.124.4469 1998/3/7 FI_ACCOUNT 7800.00 108 100
- 113 Luis Popp LPOPP 515.124.4567 1999/12/7 FI_ACCOUNT 6900.00 108 100
- 114 Den Raphaely DRAPHEAL 515.127.4561 1994/12/7 PU_MAN 11000.00 100 30
- 115 Alexander Khoo AKHOO 515.127.4562 1995/5/18 PU_CLERK 3100.00 114 30
- 116 Shelli Baida SBAIDA 515.127.4563 1997/12/24 PU_CLERK 2900.00 114 30
- 117 Sigal Tobias STOBIAS 515.127.4564 1997/7/24 PU_CLERK 2800.00 114 30
- 118 Guy Himuro GHIMURO 515.127.4565 1998/11/15 PU_CLERK 2600.00 114 30
- 119 Karen Colmenares KCOLMENA 515.127.4566 1999/8/10 PU_CLERK 2500.00 114 30
- 120 Matthew Weiss MWEISS 650.123.1234 1996/7/18 ST_MAN 8000.00 100 50
- 121 Adam Fripp AFRIPP 650.123.2234 1997/4/10 ST_MAN 8200.00 100 50
- 122 Payam Kaufling PKAUFLIN 650.123.3234 1995/5/1 ST_MAN 7900.00 100 50
- 123 Shanta Vollman SVOLLMAN 650.123.4234 1997/10/10 ST_MAN 6500.00 100 50
- 124 Kevin Mourgos KMOURGOS 650.123.5234 1999/11/16 ST_MAN 5800.00 100 50
- 125 Julia Nayer JNAYER 650.124.1214 1997/7/16 ST_CLERK 3200.00 120 50
- 126 Irene Mikkilineni IMIKKILI 650.124.1224 1998/9/28 ST_CLERK 2700.00 120 50
- 127 James Landry JLANDRY 650.124.1334 1999/1/14 ST_CLERK 2400.00 120 50
- 128 Steven Markle SMARKLE 650.124.1434 2000/3/8 ST_CLERK 2200.00 120 50
- 129 Laura Bissot LBISSOT 650.124.5234 1997/8/20 ST_CLERK 3300.00 121 50
- 130 Mozhe Atkinson MATKINSO 650.124.6234 1997/10/30 ST_CLERK 2800.00 121 50
- 131 James Marlow JAMRLOW 650.124.7234 1997/2/16 ST_CLERK 2500.00 121 50
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 132 TJ Olson TJOLSON 650.124.8234 1999/4/10 ST_CLERK 2100.00 121 50
- 133 Jason Mallin JMALLIN 650.127.1934 1996/6/14 ST_CLERK 3300.00 122 50
- 134 Michael Rogers MROGERS 650.127.1834 1998/8/26 ST_CLERK 2900.00 122 50
- 135 Ki Gee KGEE 650.127.1734 1999/12/12 ST_CLERK 2400.00 122 50
- 136 Hazel Philtanker HPHILTAN 650.127.1634 2000/2/6 ST_CLERK 2200.00 122 50
- 137 Renske Ladwig RLADWIG 650.121.1234 1995/7/14 ST_CLERK 3600.00 123 50
- 138 Stephen Stiles SSTILES 650.121.2034 1997/10/26 ST_CLERK 3200.00 123 50
- 139 John Seo JSEO 650.121.2019 1998/2/12 ST_CLERK 2700.00 123 50
- 140 Joshua Patel JPATEL 650.121.1834 1998/4/6 ST_CLERK 2500.00 123 50
- 141 Trenna Rajs TRAJS 650.121.8009 1995/10/17 ST_CLERK 3500.00 124 50
- 142 Curtis Davies CDAVIES 650.121.2994 1997/1/29 ST_CLERK 3100.00 124 50
- 143 Randall Matos RMATOS 650.121.2874 1998/3/15 ST_CLERK 2600.00 124 50
- 144 Peter Vargas PVARGAS 650.121.2004 1998/7/9 ST_CLERK 2500.00 124 50
- 145 John Russell JRUSSEL 011.44.1344.429268 1996/10/1 SA_MAN 14000.00 0.40 100 80
- 146 Karen Partners KPARTNER 011.44.1344.467268 1997/1/5 SA_MAN 13500.00 0.30 100 80
- 147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 1997/3/10 SA_MAN 12000.00 0.30 100 80
- 148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 1999/10/15 SA_MAN 11000.00 0.30 100 80
- 149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 2000/1/29 SA_MAN 10500.00 0.20 100 80
- 150 Peter Tucker PTUCKER 011.44.1344.129268 1997/1/30 SA_REP 10000.00 0.30 145 80
- 151 David Bernstein DBERNSTE 011.44.1344.345268 1997/3/24 SA_REP 9500.00 0.25 145 80
- 152 Peter Hall PHALL 011.44.1344.478968 1997/8/20 SA_REP 9000.00 0.25 145 80
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 153 Christopher Olsen COLSEN 011.44.1344.498718 1998/3/30 SA_REP 8000.00 0.20 145 80
- 154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 1998/12/9 SA_REP 7500.00 0.20 145 80
- 155 Oliver Tuvault OTUVAULT 011.44.1344.486508 1999/11/23 SA_REP 7000.00 0.15 145 80
- 156 Janette King JKING 011.44.1345.429268 1996/1/30 SA_REP 10000.00 0.35 146 80
- 157 Patrick Sully PSULLY 011.44.1345.929268 1996/3/4 SA_REP 9500.00 0.35 146 80
- 158 Allan McEwen AMCEWEN 011.44.1345.829268 1996/8/1 SA_REP 9000.00 0.35 146 80
- 159 Lindsey Smith LSMITH 011.44.1345.729268 1997/3/10 SA_REP 8000.00 0.30 146 80
- 160 Louise Doran LDORAN 011.44.1345.629268 1997/12/15 SA_REP 7500.00 0.30 146 80
- 161 Sarath Sewall SSEWALL 011.44.1345.529268 1998/11/3 SA_REP 7000.00 0.25 146 80
- 162 Clara Vishney CVISHNEY 011.44.1346.129268 1997/11/11 SA_REP 10500.00 0.25 147 80
- 163 Danielle Greene DGREENE 011.44.1346.229268 1999/3/19 SA_REP 9500.00 0.15 147 80
- 164 Mattea Marvins MMARVINS 011.44.1346.329268 2000/1/24 SA_REP 7200.00 0.10 147 80
- 165 David Lee DLEE 011.44.1346.529268 2000/2/23 SA_REP 6800.00 0.10 147 80
- 166 Sundar Ande SANDE 011.44.1346.629268 2000/3/24 SA_REP 6400.00 0.10 147 80
- 167 Amit Banda ABANDA 011.44.1346.729268 2000/4/21 SA_REP 6200.00 0.10 147 80
- 168 Lisa Ozer LOZER 011.44.1343.929268 1997/3/11 SA_REP 11500.00 0.25 148 80
- 169 Harrison Bloom HBLOOM 011.44.1343.829268 1998/3/23 SA_REP 10000.00 0.20 148 80
- 170 Tayler Fox TFOX 011.44.1343.729268 1998/1/24 SA_REP 9600.00 0.20 148 80
- 171 William Smith WSMITH 011.44.1343.629268 1999/2/23 SA_REP 7400.00 0.15 148 80
- 172 Elizabeth Bates EBATES 011.44.1343.529268 1999/3/24 SA_REP 7300.00 0.15 148 80
- 173 Sundita Kumar SKUMAR 011.44.1343.329268 2000/4/21 SA_REP 6100.00 0.10 148 80
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 174 Ellen Abel EABEL 011.44.1644.429267 1996/5/11 SA_REP 11000.00 0.30 149 80
- 175 Alyssa Hutton AHUTTON 011.44.1644.429266 1997/3/19 SA_REP 8800.00 0.25 149 80
- 176 Jonathon Taylor JTAYLOR 011.44.1644.429265 1998/3/24 SA_REP 8600.00 0.20 149 80
- 177 Jack Livingston JLIVINGS 011.44.1644.429264 1998/4/23 SA_REP 8400.00 0.20 149 80
- 178 Kimberely Grant KGRANT 011.44.1644.429263 1999/5/24 SA_REP 7000.00 0.15 149
- 179 Charles Johnson CJOHNSON 011.44.1644.429262 2000/1/4 SA_REP 6200.00 0.10 149 80
- 180 Winston Taylor WTAYLOR 650.507.9876 1998/1/24 SH_CLERK 3200.00 120 50
- 181 Jean Fleaur JFLEAUR 650.507.9877 1998/2/23 SH_CLERK 3100.00 120 50
- 182 Martha Sullivan MSULLIVA 650.507.9878 1999/6/21 SH_CLERK 2500.00 120 50
- 183 Girard Geoni GGEONI 650.507.9879 2000/2/3 SH_CLERK 2800.00 120 50
- 184 Nandita Sarchand NSARCHAN 650.509.1876 1996/1/27 SH_CLERK 4200.00 121 50
- 185 Alexis Bull ABULL 650.509.2876 1997/2/20 SH_CLERK 4100.00 121 50
- 186 Julia Dellinger JDELLING 650.509.3876 1998/6/24 SH_CLERK 3400.00 121 50
- 187 Anthony Cabrio ACABRIO 650.509.4876 1999/2/7 SH_CLERK 3000.00 121 50
- 188 Kelly Chung KCHUNG 650.505.1876 1997/6/14 SH_CLERK 3800.00 122 50
- 189 Jennifer Dilly JDILLY 650.505.2876 1997/8/13 SH_CLERK 3600.00 122 50
- 190 Timothy Gates TGATES 650.505.3876 1998/7/11 SH_CLERK 2900.00 122 50
- 191 Randall Perkins RPERKINS 650.505.4876 1999/12/19 SH_CLERK 2500.00 122 50
- 192 Sarah Bell SBELL 650.501.1876 1996/2/4 SH_CLERK 4000.00 123 50
- 193 Britney Everett BEVERETT 650.501.2876 1997/3/3 SH_CLERK 3900.00 123 50
- 194 Samuel McCain SMCCAIN 650.501.3876 1998/7/1 SH_CLERK 3200.00 123 50
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
- 195 Vance Jones VJONES 650.501.4876 1999/3/17 SH_CLERK 2800.00 123 50
- 196 Alana Walsh AWALSH 650.507.9811 1998/4/24 SH_CLERK 3100.00 124 50
- 197 Kevin Feeney KFEENEY 650.507.9822 1998/5/23 SH_CLERK 3000.00 124 50
- 107 rows selected
- SQL>
- Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
- Connected as system@ORCL
- SQL> set serveroutput on;
- SQL>
- SQL> declare
- 2 v_sum number:=0;
- 3 begin
- 4 for v_counter in 1..100 loop
- 5 if mod(v_counter,2)=0 then
- 6 v_sum:=v_sum+v_counter;
- 7 end if;
- 8 end loop;
- 9 dbms_output.put_line(v_sum);
- 10 end;
- 11 /
- 2550
- PL/SQL procedure successfully completed
- SQL>
- SQL> Declare
- 2 v_deptno hr.employees.department_id%type;
- 3 v_increment Number(4);
- 4 v_empno hr.employees.employee_id%type;
- 5 begin
- 6 v_empno:=&x;
- 7 select department_id into v_deptno from hr.employees
- 8 where employee_id=v_empno;
- 9 if v_deptno=10 then v_increment:=100;
- 10 elsif v_deptno=20 then v_increment:=140;
- 11 elsif v_deptno=30 then v_increment:=200;
- 12 else v_increment:=300;
- 13 end if;
- 14 update hr.employees set salary=salary+v_increment
- 15 where employee_id=v_empno;
- 16 end;
- 17 /
- PL/SQL procedure successfully completed
- SQL>
- SQL> declare
- 2 v_deptno hr.employees.department_id%type;
- 3 cursor c_emp is select * from hr.employees where department_id=v_deptno;
- 4 v_emp c_emp%rowtype;
- 5 begin
- 6 v_deptno:=&x;
- 7 open c_emp;
- 8 loop
- 9 fetch c_emp into v_emp;
- 10 exit when c_emp%notfound;
- 11 dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||
- 12 ' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
- 13 end loop;
- 14 close c_emp;
- 15 end;
- 16 /
- PL/SQL procedure successfully completed
- SQL>
- SQL> declare
- 2 cursor c_dept_stat is
- 3 select department_id,avg(salary) avgsal from hr.employees
- 4 group by department_id;
- 5 v_dept c_dept_stat%rowtype;
- 6 begin
- 7 open c_dept_stat;
- 8 loop
- 9 fetch c_dept_stat into v_dept;
- 10 exit when c_dept_stat%notfound;
- 11 dbms_output.put_line(v_dept.department_id||' '||v_dept.avgsal);
- 12 end loop;
- 13 close c_dept_stat;
- 14 end;
- 15 /
- 100 8600
- 30 4150
- 7000
- 20 9500
- 70 10000
- 90 19333.3333333333333333333333333333333333
- 110 10150
- 50 3496.666666666666666666666666666666666667
- 40 6500
- 80 8955.882352941176470588235294117647058824
- 10 4400
- 60 5760
- PL/SQL procedure successfully completed
- SQL>
- SQL> declare
- 2 cursor c_dept_stat is
- 3 select department_id,avg(salary) avgsal from hr.employees
- 4 group by department_id;
- 5 v_dept c_dept_stat%rowtype;
- 6 begin
- 7 open c_dept_stat;
- 8 fetch c_dept_stat into v_dept;
- 9 while c_dept_stat%found loop
- 10 dbms_output.put_line(v_dept.department_id||' '||v_dept.avgsal);
- 11 fetch c_dept_stat into v_dept;
- 12 end loop;
- 13 close c_dept_stat;
- 14 end;
- 15 /
- 100 8600
- 30 4150
- 7000
- 20 9500
- 70 10000
- 90 19333.3333333333333333333333333333333333
- 110 10150
- 50 3496.666666666666666666666666666666666667
- 40 6500
- 80 8955.882352941176470588235294117647058824
- 10 4400
- 60 5760
- PL/SQL procedure successfully completed
- SQL>
- SQL> declare
- 2 cursor c_dept_stat is
- 3 select department_id,avg(salary) avgsal from hr.employees
- 4 group by department_id;
- 5 begin
- 6 for v_dept in c_dept_stat loop
- 7 dbms_output.put_line(v_dept.department_id||' '||v_dept.avgsal);
- 8 end loop;
- 9 end;
- 10 /
- 100 8600
- 30 4150
- 7000
- 20 9500
- 70 10000
- 90 19333.3333333333333333333333333333333333
- 110 10150
- 50 3496.666666666666666666666666666666666667
- 40 6500
- 80 8955.882352941176470588235294117647058824
- 10 4400
- 60 5760
- PL/SQL procedure successfully completed
- SQL>
草稿：
- Declare
- v_deptno hr.employees.department_id%type;
- v_increment Number(4);
- v_empno hr.employees.employee_id%type;
- begin
- v_empno:=&x;
- select department_id into v_deptno from hr.employees
- where employee_id=v_empno;
- if v_deptno=10 then v_increment:=100;
- elsif v_deptno=20 then v_increment:=140;
- elsif v_deptno=30 then v_increment:=200;
- else v_increment:=300;
- end if;
- update hr.employees set salary=salary+v_increment
- where employee_id=v_empno;
- end;
- /
- Declare
- v_sal hr.employees.salary%type;
- v_increment Number(4);
- v_empno hr.employees.employee_id%type;
- begin
- v_empno:=&x;
- select salary into v_sal from hr.employees
- where employee_id=v_empno;
- case
- when v_sal<1000 then v_increment:=200;
- when v_sal<1000 then v_increment:=140;
- when v_sal<1000 then v_increment:=100;
- else v_increment:=50;
- end case;
- update hr.employees set salary=salary+v_increment
- where employee_id=v_empno;
- end;
- [9-11]
- declare
- v_counter binary_integer :=1;
- v_sum number :=0;
- begin
- loop
- if mod(v_counter,2)=0 then
- v_sum:=v_sum+v_counter;
- end if;
- v_counter :=v_counter+1;
- exit when v_counter>100;
- end loop;
- dbms_output.put_line(v_sum);
- end;
- Declare
- v_counter Binary_Integer:=1;
- v_sum number:=0;
- begin
- loop
- if mod(v_counter,2)=0 then
- v_sum:=v_sum+v_counter;
- end if;
- v_counter:=v_counter+1;
- exit when v_counter>100;
- end loop;
- Dbms_Output.put_line(v_sum);
- end;
- declare
- v_counter binary_integer :=1;
- v_sum number :=0;
- begin
- while v_counter <=100 loop
- if mod(v_counter,2)=0 then
- v_sum:=v_sum+v_counter;
- end if;
- v_counter:=v_counter+1;
- end loop;
- dbms_output.put_line(v_sum);
- end;
- declare
- v_sum number:=0;
- begin
- for v_counter in 1..100 loop
- if mod(v_counter,2)=0 then
- v_sum:=v_sum+v_counter;
- end if;
- end loop;
- dbms_output.put_line(v_sum);
- end;
- declare
- v_deptno hr.employees.department_id%type;
- cursor c_emp is select * from hr.employees where department_id=v_deptno;
- v_emp c_emp%rowtype;
- begin
- v_deptno:=&x;
- open c_emp;
- loop
- fetch c_emp into v_emp;
- exit when c_emp%notfound;
- dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||
- ' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
- end loop;
- close c_emp;
- end;
第七周：
- 创建永久性表空间
- 段默认为自动管理
- 加个manual为手动管理
- 区默认为自动分配
- 加个uniform为定制分配
- 表空间分为dictionary(字典管理)
- 本地管理(local)
- SQL>
- SQL> Create tablespace HRTBS1 datafile
- 2 'E:\w.DBF' size 50M;
- Tablespace created
- SQL>
- SQL> create tablespace HRTBS2 datafile
- 2 'E:\RRR.DBF' size 50M
- 3 extent Management local uniform size 512K;
- Tablespace created
- SQL>
- SQL> create tablespace HRTBS3 datafile
- 2 'E:\fgg.DBF' size 50M
- 3 segment space management manual;
- //段手动管理
- Tablespace created
- SQL>
- SQL> create tablespace HRTBS4 datafile
- 2 'E:\ADD.DBF' size 50M
- 3 extent management local uniform size 512k
- 4 segment space management manual;
- Tablespace created
- SQL>
- SQL> create tablespace HRTBS5 datafile
- 2 'E:\ohoho.DBF' size 50M;
- Tablespace created
- SQL>
- SQL> create tablespace indx datafile
- 2 'E:\yyyy.DBF' size 50M;
- Tablespace created
- SQL> /*创建大文件表空间(此时段管理只能采用自动管理)
- SQL> create bigfile tablespace big_tbs
- 2 datafile 'E:\ppppp.DBF' size 50M
- 3 uniform size 512k;
- Tablespace created
- SQL> /*创建临时表空间(此时区管理只能采用自动分配)
- SQL> create temporary tablespace HRTEMP1 TEMPFILE
- 2 'E:\uuu.DBF' size 20M
- 3 extent management local uniform size 15M;
- Tablespace created
- SQL>
- SQL> create temporary tablespace HRTEMP2 TEMPFILE
- 2 'E:\ddddd.DBF' size 20M
- 3 extent management local uniform size 15M
- 4 tablespace group temp_group;
- Tablespace created
- SQL>
- SQL> alter tablespace HRTEMP1 tablespace group temp_group;
- Tablespace altered
- SQL> //撤销表空间的区只能采用自动分配
- SQL> create undo tablespace HRUNDO1 DATAFILE
- 2 'E:\ccc.DBF' SIZE 20M;
- Tablespace created
- SQL> //添加数据文件的大小
- SQL> alter tablespace users ADD C
- 2 'E:\ty.DBF' size 10M;
- Tablespace altered
- SQL>
- SQL> alter tablespace temp ADD TEMPFILE
- 2 'E:\T.DBF' SIZE 5M;
- Tablespace altered
- -------------------------------------------------------------------
- SQL> //改变数据文件的扩展性
- SQL> ALTER DATABASE TEMPFILE
- 2 'E:\T.DBF'
- 3 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
- Database altered
- SQL>
- SQL> ALTER DATABASE TEMPFILE
- 2 'E:\T.DBF'
- 3 AUTOEXTEND OFF;
- ----------------------------------------------------------------------
- Database altered
- SQL> //重新设置文件大小
- SQL> ALTER DATABASE DATAFILE
- 2 'E:\ty.DBF' RESIZE 8M;
- Database altered
- SQL>
- SQL> ALTER TABLESPACE USERS OFFLINE;
- Tablespace altered
- SQL>
- SQL> ALTER TABLESPACE USERS ONLINE;
- Tablespace altered
- SQL>
- SQL> ALTER TABLESPACE USERS READ ONLY;
- Tablespace altered
- SQL>
- SQL> ALTER TABLESPACE USERS READ WRITE;
- Tablespace altered
- SQL>
- SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE HRTEMP1;
- Database altered
- SQL>
- SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP;
- Database altered
- SQL>
- SQL> alter tablespace hrtbs1 begin backup
- SQL>
- SQL> select tablespace_name,extent_management,allocation_type,contents from DBA_tablespaces;
- TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE CONTENTS
- ------------------------------ ----------------- --------------- ---------
- SYSTEM LOCAL SYSTEM PERMANENT
- UNDOTBS1 LOCAL SYSTEM UNDO
- SYSAUX LOCAL SYSTEM PERMANENT
- TEMP LOCAL UNIFORM TEMPORARY
- USERS LOCAL SYSTEM PERMANENT
- EXAMPLE LOCAL SYSTEM PERMANENT
- HRTBS1 LOCAL SYSTEM PERMANENT
- 7 rows selected
- SQL>
- SQL> alter tablespace users add datafile
- 2 'E:\o.DBF' size 5m;
- Tablespace altered
- SQL>
- SQL> alter tablespace temp add tempfile
- 2 'E:\l.DBF' size 5m;
- Tablespace altered
-
相关阅读:
SQL Thinking
基于混合整数二阶锥规划配电网重构的求解（Python代码实现）
Redis中protected-mode模式详解
基于华为ensp的企业网络规划（新版）
springcloud: stream整合rocketmq
FS2119A同步升压IC输出3.3V和FS2119B同步升压IC输出5V
javascript二叉树相关的知识
Spring依赖注入的三种方式
阿里云 压缩包 无法分享 解决方案
《软件质量保证与测试》第 6 章——系统测试 重点部分总结
- 原文地址：https://blog.csdn.net/m0_63064861/article/details/134079499
