索引练习笔记：
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
STATSPACK
7 rows selected.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/RHYS/users01.dbf
/opt/app/oracle/oradata/RHYS/undotbs01.dbf
/opt/app/oracle/oradata/RHYS/sysaux01.dbf
/opt/app/oracle/oradata/RHYS/system01.dbf
/opt/app/oracle/oradata/RHYS/example01.dbf
/opt/app/oracle/oradata/RHYS/statspack.dbf
6 rows selected.
SQL> create tablespace rhys datafile '/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf' size 50M autoextend on next 2M maxsize 500M;
create tablespace rhys datafile '/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf' size 50M autoextend on next 2M maxsize 500M
*
ERROR at line 1:
ORA-01119: error in creating database file
'/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf'
ORA-27044: unable to write the header block of file
Linux-x86_64 Error: 28: No space left on device
Additional information: 3
[oracle@oracle-one ~]$ df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/vg_oracleone-lv_root
5168516 3826384 1079580 78% /
tmpfs 605636 287780 317856 48% /dev/shm
/dev/sda1 495844 56256 413988 12% /boot
/dev/mapper/vg_oracleone-LogVol02
5168516 144984 4760980 3% /home
/dev/mapper/vg_oracleone-LogVol03
12925656 12269068 0 100% /opt
[oracle@oracle-one ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_oracleone-lv_root
5.0G 3.7G 1.1G 78% /
tmpfs 592M 282M 311M 48% /dev/shm
/dev/sda1 485M 55M 405M 12% /boot
/dev/mapper/vg_oracleone-LogVol02
5.0G 142M 4.6G 3% /home
/dev/mapper/vg_oracleone-LogVol03
13G 12G 0 100% /opt
[oracle@oracle-one opt]$ cd oracle
[oracle@oracle-one oracle]$ ls
database p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip
[oracle@oracle-one oracle]$ rm -rf *
[oracle@oracle-one oracle]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_oracleone-lv_root
5.0G 3.7G 1.1G 78% /
tmpfs 592M 282M 311M 48% /dev/shm
/dev/sda1 485M 55M 405M 12% /boot
/dev/mapper/vg_oracleone-LogVol02
5.0G 142M 4.6G 3% /home
/dev/mapper/vg_oracleone-LogVol03
13G 6.9G 4.9G 59% /opt
[oracle@oracle-one oracle]$ exit
exit
SQL> l
1* create tablespace rhys datafile '/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf' size 50M autoextend on next 2M maxsize 500M
SQL> r
1* create tablespace rhys datafile '/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf' size 50M autoextend on next 2M maxsize 500M
Tablespace created.
SQL> append temp
1* create user rhys identified by amy default tablespace rhys temporary tablespace temp
SQL> r
1* create user rhys identified by amy default tablespace rhys temporary tablespace temp
User created.
SQL> grant select any table to rhys;
Grant succeeded.
SQL> grant create session to rhys with admin option;
Grant succeeded.
SQL> grant resource to rhys;
Grant succeeded.
SQL> conn rhys/amy
Connected.
SQL> select default_tablespace ,username from user_users where username=upper('rhys');
DEFAULT_TABLESPACE USERNAME
------------------------------ ------------------------------
RHYS RHYS
SQL>
SQL> select owner,table_name from all_tables where owner=upper('scott');
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT DEPT
SCOTT EMP
SCOTT BONUS
SCOTT SALGRADE
SQL> create table amy_dept as select * from scott.dept;
Table created.
SQL> create table amy_emp as select * from scott.emp;
Table created.
SQL> create table amy_bonus as select * from scott.bonus;
Table created.
SQL> create table amy_salgrade as select * from scott.salgrade;
Table created.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
AMY_DEPT
AMY_EMP
AMY_BONUS
AMY_SALGRADE
SQL> conn / as sysdba
Connected.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/RHYS/users01.dbf
/opt/app/oracle/oradata/RHYS/undotbs01.dbf
/opt/app/oracle/oradata/RHYS/sysaux01.dbf
/opt/app/oracle/oradata/RHYS/system01.dbf
/opt/app/oracle/oradata/RHYS/example01.dbf
/opt/app/oracle/oradata/RHYS/statspack.dbf
/opt/app/oracle/oradata/RHYS/Rhys_Amy.dbf
7 rows selected.
SQL> create tablespace index_tablespace datafile '/opt/app/oracle/oradata/RHYS/index_tablespace.dbf' size 60M autoextend off;
Tablespace created.
SQL> conn rhys/amy
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
AMY_DEPT
AMY_EMP
AMY_BONUS
AMY_SALGRADE
SQL> select * from amy_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> conn rhys/amy
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
AMY_DEPT
AMY_EMP
AMY_BONUS
AMY_SALGRADE
SQL> desc amy_salgrade
Name Null? Type
----------------------------------------- -------- ----------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> alter table amy_salgrade modify grade not null;
Table altered.
SQL> select index_name,index_type,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
EMPNO_INDEX FUNCTION-BASED NORMAL AMY_EMP
SQL> select grade from amy_salgrade group by grade having count(*) >1;
no rows selected
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade(GRADE) tablespace index_tablespace;
Index created.
SQL>
SQL> select GRADE,count(*) from rhys.amy_salgrade group by GRADE having count(*) >1;
no rows selected
SQL> drop index unique_index_salgrade;
Index dropped.
SQL> select * from amy_salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> alter table amy_salgrade modify grade null;
Table altered.
SQL> create unique index unique_index_salgrade on rhys.amy_salgrade(GRADE) tablespace index_tablespace;
Index created.
SQL> select * from amy_bonus;
no rows selected
SQL> desc amy_bonus;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
SQL> create index index_bonnus on amy_bonus(ename,job) tablespace index_tablespace;
Index created.
SQL> select index_name,index_type,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
UNIQUE_INDEX_SALGRADE NORMAL AMY_SALGRADE
EMPNO_INDEX FUNCTION-BASED NORMAL AMY_EMP
INDEX_BONNUS NORMAL AMY_BONUS
SQL> alter table amy_emp add esex varchar2(20) not null;
alter table amy_emp add esex varchar2(20) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
SQL> c /not null
1* alter table amy_emp add esex varchar2(20)
SQL> r
1* alter table amy_emp add esex varchar2(20)
Table altered.
SQL> create bitmap index bitmap_emp on amy_emp(esex) tablespace index_tablespace;
Index created.
SQL> r
1* select index_name,index_type,table_owner,table_name from user_indexes
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
UNIQUE_INDEX_SALGRADE NORMAL RHYS AMY_SALGRADE
EMPNO_INDEX FUNCTION-BASED NORMAL RHYS AMY_EMP
BITMAP_EMP BITMAP RHYS AMY_EMP
INDEX_BONNUS NORMAL RHYS AMY_BONUS
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
AMY_DEPT
AMY_EMP
AMY_BONUS
AMY_SALGRADE
SQL> desc amy_dept;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> create index reverse_index on amy_dept(deptno) reverse tablespace index_tablespace;
Index created.
SQL> create index function_index on amy_emp(to_char(hiredate,'yyyy-mm-dd')) tablespace index_tablespaces;
create index function_index on amy_emp(to_char(hiredate,'yyyy-mm-dd')) tablespace index_tablespaces
*
ERROR at line 1:
ORA-00959: tablespace 'INDEX_TABLESPACES' does not exist
SQL> c /index_tablespaces/index_tablespace
1* create index function_index on amy_emp(to_char(hiredate,'yyyy-mm-dd')) tablespace index_tablespace
SQL> r
1* create index function_index on amy_emp(to_char(hiredate,'yyyy-mm-dd')) tablespace index_tablespace
Index created.
SQL> select index_name,index_type,table_name,table_owner from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_OWNER
------------------------------ --------------------------- ------------------------------ ------------------------------
UNIQUE_INDEX_SALGRADE NORMAL AMY_SALGRADE RHYS
EMPNO_INDEX FUNCTION-BASED NORMAL AMY_EMP RHYS
BITMAP_EMP BITMAP AMY_EMP RHYS
FUNCTION_INDEX FUNCTION-BASED NORMAL AMY_EMP RHYS
REVERSE_INDEX NORMAL/REV AMY_DEPT RHYS
INDEX_BONNUS NORMAL AMY_BONUS RHYS
6 rows selected.
SQL> alter index empno_index coalesce deallocate unused;
Index altered.
SQL> alter index empno_index rebuild;
Index altered.
SQL> alter index empno_index monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
EMPNO_INDEX AMY_EMP YES NO 09/15/2013 22:13:02
SQL> alter index empno_index nomonitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
EMPNO_INDEX AMY_EMP NO NO 09/15/2013 22:13:02 09/15/2013 22:13:39
2417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
