oracle switches temporary table spaces, creates indexes, creates temporary tables, and creates large and small table spaces

1. The temporary table space of the switched database is temp1/the undo table space of the switched database is undotbs01

1. View the table space of the current database

select tablespace_name from dba_tablespaces;

It was found that there are no above two table spaces.

2. Create temp1 temporary table space and undotbs01 table space

#Create temp1 temporary table space
create temporary tablespace temp1 tempfile '/home/oracle/oradata/orcl/temp1.dbf'
size 100m autoextend on NEXT 10M MAXSIZE UNLIMITED;
#Create undotbs01 table space
create undo tablespace undotbs01 datafile '/home/oracle/oradata/orcl/undotbs01_01.dbf'
size 100m autoextend on NEXT 10M MAXSIZE UNLIMITED;

View the current database table space again

select tablespace_name from dba_tablespaces;

Created successfully!

3. Switch two table spaces

#Switch temp1 table space
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

#Switch undotbs01_01 table space
ALTER SYSTEM SET undo_tablespace = undotbs01;

The modification is successful, then check whether the switch is successful

#View temp
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
#viewundo
SELECT TABLESPACE_NAME, STATUS
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'UNDOTBS01';

Successfully modified!

2. Create two simple tables A and B. Tables A and B have associated columns, and indexes are created on the associated columns.

1. First create two simple tables

CREATE TABLE Table1 (
    ID NUMBER PRIMARY KEY,
    Name VARCHAR2(50)
);

CREATE TABLE Table2 (
    ID NUMBER PRIMARY KEY,
    Table1_ID NUMBER,
    data VARCHAR2(50),
    FOREIGN KEY (Table1_ID) REFERENCES Table1(ID)
);

2. To table1 and table2 respectively Add data

--Insert data into Table1
INSERT INTO Table1 (ID, Name) VALUES (1, 'John');
INSERT INTO Table1 (ID, Name) VALUES (2, 'Emily');
INSERT INTO Table1 (ID, Name) VALUES (3, 'Michael');

-- Insert data into Table2
INSERT INTO Table2 (ID, Table1_ID, data) VALUES (101, 1, 'Data related to John');
INSERT INTO Table2 (ID, Table1_ID, data) VALUES (102, 1, 'Additional data for John');
INSERT INTO Table2 (ID, Table1_ID, data) VALUES (103, 2, 'Data related to Emily');

3. View the data in the above two tables

select * from table1;
select * from table2;

4. Create corresponding index

1. Create an index on the Table1_ID column of Table2:

CREATE INDEX idx_Table2_Table1_ID ON Table2(Table1_ID);

This will create an index named on Table2’s Table1_ID column to speed up queries on Table2’s Table1_ID column. idx_Table2_Table1_ID

5.Use of index

SELECT *
FROM Table2
WHERE Table1_ID = 1
ORDER BY ID;

3. Create a transaction-level temporary table or session-level temporary table, and test under what circumstances the data in the temporary table will disappear

Introduction to transaction-level temporary tables and session-level temporary tables

Transaction-level temporary tables: Transaction-level temporary tables are created within a specific database transaction, and their life cycle is limited to that transaction. The data in transaction-level temporary tables will be automatically cleared at the end of the transaction, regardless of whether the transaction is submitted normally or rolled back.

Data is automatically cleared when the transaction completes (commit or rollback).

Session-level temporary tables: Session-level temporary tables are created in the database session, and their life cycle is related to the database session. This means that multiple transactions within the same session can access the same session-level temporary tables, and the data for these tables will be cleared at the end of the session. If the database session is terminated, the data will be cleared.

Transaction-level temporary table:

1. Create transaction-level temporary table

CREATE GLOBAL TEMPORARY TABLE transaction_temp_table (
  ID NUMBER,
  name VARCHAR2(50)
) ON COMMIT DELETE ROWS;

2. Insert test data

insert into transaction_temp_table values(1,'test1');
insert into transaction_temp_table values(2,'test2');
insert into transaction_temp_table values(3,'test3');

3. View data in the table

select * from transaction_temp_table;

4. Submit transaction

commit;

5. View the data in the table again

select * from transaction_temp_table;

The data has been cleared after submission.

Session-level temporary table:

1. Create session-level temporary table

CREATE GLOBAL TEMPORARY TABLE session_temp_table (
  ID NUMBER,
  name VARCHAR2(50)
) ON COMMIT PRESERVE ROWS;

2. Insert test data

insert into session_temp_table values(1,'test1');
insert into session_temp_table values(2,'test2');
insert into session_temp_table values(3,'test3');

3. View data in the table

select * from session_temp_table; 

4. Submit the transaction and view the data again
Commit transaction

commit;

View the data in the table again

select * from session_temp_table;

This is different from a transaction-level temporary table

5. Close the current session

quit()

6. Enter the database again to create a session

sqlplus / as sysdba

7. View the data in the table again

select * from session_temp_table; 

The data will be deleted after restarting the session.

4. Create a small table table space A and a large table table space B. And create a user B1 with the default table space as B

--Create small table table space A
CREATE TABLESPACE A
DATAFILE '/home/oracle/oradata/orcl/datafile_A.dbf'
SIZE 10M;
 
--Create large table table space B
CREATE TABLESPACE B
DATAFILE '/home/oracle/oradata/orcl/datafile_B.dbf'
SIZE 1G;
 
--Create user B1 and set its default tablespace to B
CREATE USER B1 IDENTIFIED BY password
DEFAULT TABLESPACE B
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON B;
 
-- Grant permissions to user B1
GRANT CONNECT, RESOURCE TO B1;