Oracle PL/SQL/Table/Table space

Материал из SQL эксперт
Перейти к: навигация, поиск

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>