oracle第八周实验--2023-10-27

怼烎@ 2024-02-20 09:28 2阅读 0赞

## 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