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
<source lang="sql">
SQL> alter tablespace RBS
2 default storage 3 (initial 125K next 125K minextents 18 maxextents 249) 4
SQL>
</source>
Change user default table space and temporary table space
<source lang="sql">
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>
</source>
Check space with show_space for an index with compress 1
<source lang="sql">
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> --
</source>
Check space with show_space for an index with compress 2
<source lang="sql">
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> --
</source>
Create two indexes on one table and check the space
<source lang="sql">
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> --
</source>
Get default table space for current user
<source lang="sql">
SQL> SQL> select default_tablespace from user_users; DEFAULT_TABLESPACE
SYSTEM SQL>
</source>
heap table
<source lang="sql">
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>
</source>
Map user objects to tablespaces.
<source lang="sql">
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>
</source>
Measure the fragmentation of free space in all of the tablespaces in a database
<source lang="sql">
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>
</source>
Moving Tables To New Tablespaces or Storage
<source lang="sql">
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>
</source>
Show_space
<source lang="sql">
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> --
</source>
The plsql user is created using the USERS and TEMP tablespace.
<source lang="sql">
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>
</source>