Oracle PL/SQL/Table/Table space
Содержание
- 1 alter tablespace
- 2 Change user default table space and temporary table space
- 3 Check space with show_space for an index with compress 1
- 4 Check space with show_space for an index with compress 2
- 5 Create two indexes on one table and check the space
- 6 Get default table space for current user
- 7 heap table
- 8 Map user objects to tablespaces.
- 9 Measure the fragmentation of free space in all of the tablespaces in a database
- 10 Moving Tables To New Tablespaces or Storage
- 11 Show_space
- 12 The plsql user is created using the USERS and TEMP tablespace.
alter tablespace
SQL> alter tablespace RBS
2 default storage
3 (initial 125K next 125K minextents 18 maxextents 249)
4
SQL>
Change user default table space and temporary table space
SQL> create user oracle_admin identified by oracle_admin;
User created.
SQL>
SQL> grant create session, dba to oracle_admin;
Grant succeeded.
SQL>
SQL> alter user oracle_admin
2 default tablespace users
3 temporary tablespace temp;
User altered.
SQL>
SQL>
SQL> -- connect oracle_admin/oracle_admin;
SQL>
SQL> DROP USER oracle_admin;
User dropped.
SQL>
Check space with show_space for an index with compress 1
SQL>
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> create index t_idx on
2 t(owner,object_type,object_name)
3 compress 1;
Index created.
SQL>
SQL> exec show_space("T_IDX",user,"INDEX")
Free Blocks
0
Total Blocks
64
Total Bytes
524288
Unused Blocks
0
Unused Bytes
0
Last Used Ext FileId
1
Last Used Ext BlockId
42889
Last Used Block
8
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop index t_idx;
Index dropped.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Check space with show_space for an index with compress 2
SQL>
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> create index t_idx on
2 t(owner,object_type,object_name)
3 compress 2;
Index created.
SQL>
SQL> exec show_space("T_IDX",user,"INDEX")
Free Blocks
0
Total Blocks
56
Total Bytes
458752
Unused Blocks
4
Unused Bytes
32768
Last Used Ext FileId
1
Last Used Ext BlockId
42753
Last Used Block
4
PL/SQL procedure successfully completed.
SQL>
SQL> drop index t_idx;
Index dropped.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Create two indexes on one table and check the space
SQL>
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> create table t
2 nologging
3 as
4 select * from all_objects;
Table created.
SQL>
SQL> create index t_idx_1 on t(owner,object_type,object_name)
2 nologging pctfree 0;
Index created.
SQL>
SQL> create index t_idx_2 on t(object_name,object_type,owner)
2 nologging pctfree 0;
Index created.
SQL>
SQL> exec show_space( "T_IDX_1", user, "INDEX" );
Free Blocks
0
Total Blocks
72
Total Bytes
589824
Unused Blocks
6
Unused Bytes
49152
Last Used Ext FileId
1
Last Used Ext BlockId
42897
Last Used Block
2
PL/SQL procedure successfully completed.
SQL> exec show_space( "T_IDX_2", user, "INDEX" );
Free Blocks
0
Total Blocks
72
Total Bytes
589824
Unused Blocks
6
Unused Bytes
49152
Last Used Ext FileId
1
Last Used Ext BlockId
42969
Last Used Block
2
PL/SQL procedure successfully completed.
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> declare
2 cnt int;
3 begin
4 for x in ( select owner, object_type, object_name from t )
5 loop
6 select count(*) into cnt
7 from t
8 where object_name = x.object_name
9 and object_type = x.object_type
10 and owner = x.owner;
11
12 select count(*) into cnt
13 from t
14 where object_name = x.object_name
15 and object_type = x.object_type
16 and owner = x.owner;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Get default table space for current user
SQL>
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
SYSTEM
SQL>
heap table
SQL>
SQL>
SQL> create table subjects (
2 subject_id number not null,
3 subject_name varchar2(30) not null,
4 description varchar2(4000)
5 )
6 tablespace users;
Table created.
SQL>
SQL> alter table subjects add constraint pk_subjects primary key (subject_id);
Table altered.
SQL>
SQL> create table courses (
2 course_id number not null,
3 course_name varchar2(60) not null,
4 subject_id number not null,
5 duration number(2),
6 skill_lvl varchar2(12) not null
7 )
8 tablespace users;
Table created.
SQL>
SQL> alter table courses add constraint pk_courses primary key (course_id);
Table altered.
SQL>
SQL> alter table courses add constraint fk_course_subj foreign key (subject_id) references subjects (subject_id);
Table altered.
SQL>
SQL> alter table courses add constraint ck_level check(
2 skill_lvl in ("BEGINNER", "INTERMEDIATE", "ADVANCED")
3 );
Table altered.
SQL>
SQL> drop table subjects cascade constraints;
Table dropped.
SQL> drop table courses cascade constraints;
Table dropped.
SQL>
Map user objects to tablespaces.
SQL>
SQL> set pagesize 120
SQL> break on Tablespace on Owner
SQL> column Objects format A20
SQL> select
2 Tablespace_Name,
3 Owner,
4 COUNT(*)||" tables" Objects
5 from DBA_TABLES
6 group by
7 Tablespace_Name,
8 Owner
9 union
10 select
11 Tablespace_Name,
12 Owner,
13 COUNT(*)||" indexes" Objects
14 from DBA_INDEXES
15 group by
16 Tablespace_Name,
17 Owner;
TABLESPACE_NAME OWNER OBJECTS
------------------------------ ------------------------------ --------------------
SYSAUX CTXSYS 26 tables
SYSAUX 47 indexes
SYSAUX DBSNMP 17 tables
SYSAUX 8 indexes
SYSAUX FLOWS_020100 160 tables
SYSAUX 422 indexes
SYSAUX FLOWS_FILES 1 tables
SYSAUX 5 indexes
SYSAUX SYS 175 tables
SYSAUX 247 indexes
SYSAUX SYSTEM 21 indexes
SYSAUX 22 tables
SYSAUX TSMSYS 1 tables
SYSAUX 2 indexes
SYSAUX XDB 11 tables
SYSAUX 384 indexes
SYSTEM DEFINER 1 indexes
SYSTEM INV10 1 indexes
SYSTEM INV11 1 indexes
SYSTEM INV12 1 indexes
SYSTEM INV13 1 indexes
SYSTEM INV14 1 indexes
SYSTEM INV15 1 indexes
SYSTEM INV16 1 indexes
SYSTEM INV17 1 indexes
SYSTEM INV18 1 indexes
SYSTEM INV19 1 indexes
SYSTEM INV20 1 indexes
SYSTEM sqle 12 indexes
SYSTEM 14 tables
SYSTEM MDSYS 30 tables
SYSTEM 51 indexes
SYSTEM OUTLN 3 tables
SYSTEM 4 indexes
SYSTEM SYS 402 tables
SYSTEM 513 indexes
SYSTEM SYSTEM 145 indexes
SYSTEM 85 tables
USERS HR 19 indexes
USERS 6 tables
CTXSYS 11 tables
DBSNMP 2 indexes
4 tables
DEFINER 1 tables
FLOWS_020100 4 tables
HR 1 tables
INV10 1 tables
INV11 1 tables
INV12 1 tables
INV13 1 tables
INV14 1 tables
INV15 1 tables
INV16 1 tables
INV17 1 tables
INV18 1 tables
INV19 1 tables
INV20 1 tables
sqle 3 indexes
9 tables
MDSYS 3 indexes
7 tables
SYS 28 indexes
92 tables
SYSTEM 31 tables
36 indexes
XDB 1 indexes
SQL>
SQL>
Measure the fragmentation of free space in all of the tablespaces in a database
SQL> select
2 Tablespace_Name,
3 SQRT(MAX(Blocks)/SUM(Blocks))*
4 (100/SQRT(SQRT(COUNT(Blocks)))) Fsfi
5 from DBA_FREE_SPACE
6 group by
7 Tablespace_Name
8 order by 1
9
SQL> spool fsfi.lis
SQL> /
TABLESPACE_NAME FSFI
------------------------------ -------
DATA_1 8.30
SYSAUX 12.07
SYSTEM 59.31
UNDO 64.89
USERS 100.00
SQL> spool off
SQL>
Moving Tables To New Tablespaces or Storage
SQL>
SQL> create table EMP(
2 employee_id number(9),
3 first_name varchar2(15),
4 last_name varchar2(20),
5 email varchar2(25),
6 constraint pk_people primary key (employee_id)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> select tablespace_name, table_name
2 from user_tables
3 where table_name in ("EMP", "DEPT")
4 order by 1, 2;
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM EMP
SQL>
SQL>
SQL> select segment_name, tablespace_name
2 from user_segments
3 where segment_name = "EMP";
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
EMP SYSTEM
SQL>
SQL> alter table emp move
2 tablespace users;
Table altered.
SQL>
SQL> select segment_name, tablespace_name
2 from user_segments
3 where segment_name = "EMP";
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
EMP USERS
SQL>
SQL> drop table EMP;
Table dropped.
SQL>
SQL>
Show_space
SQL>
SQL>
SQL>
SQL>
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> create index t_idx on t(owner,object_type,object_name);
Index created.
SQL>
SQL> exec show_space("T_IDX",user,"INDEX")
Free Blocks
0
Total Blocks
80
Total Bytes
655360
Unused Blocks
7
Unused Bytes
57344
Last Used Ext FileId
1
Last Used Ext BlockId
42905
Last Used Block
1
PL/SQL procedure successfully completed.
SQL>
SQL> drop index t_idx;
Index dropped.
SQL>
SQL> drop table t;
Table dropped.
SQL> --
The plsql user is created using the USERS and TEMP tablespace.
SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> SPOOL create_user.log
SQL>
SQL> DECLARE
2 v_count INTEGER := 0;
3 v_statement VARCHAR2 (500);
4 BEGIN
5
6
7
8 v_statement := "CREATE USER &username IDENTIFIED BY oracle"
9 || " DEFAULT TABLESPACE &default_ts"
10 || " TEMPORARY TABLESPACE &temp_ts"
11 || " QUOTA UNLIMITED ON &default_ts"
12 || " ACCOUNT UNLOCK";
13
14 EXECUTE IMMEDIATE (v_statement);
15
16
17 EXECUTE IMMEDIATE ("GRANT connect, resource TO &username");
18 EXECUTE IMMEDIATE ("GRANT CTXAPP TO &username");
19
20 DBMS_OUTPUT.put_line (" ");
21 DBMS_OUTPUT.put_line ("User &username created successfully");
22 DBMS_OUTPUT.put_line (" ");
23
24 EXCEPTION
25 WHEN OTHERS
26 THEN
27 DBMS_OUTPUT.put_line (SQLERRM);
28 DBMS_OUTPUT.put_line (" ");
29 END;
30 /
User plsql created successfully
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL>
SQL> SPOOL OFF
SQL>
SQL>