Oracle-related operations, storage, temporary tablespace, user action, startup process

Tablespace: This space is used for data storage (tables, function, stored procedures, etc.), so is the actual physical storage area.
Temporal tablespace: The primary purpose is to sort operations on the database [such as creating indexes, order by and group BY, DISTINCT, union/intersect/minus/, Sort-merge and joins, analyze commands], managing indexes [ such as creating an index, IMP for data import], access to views and other operations to provide temporary computing space, when the operation is complete, the system will automatically clean up.

1. Querying table space for remaining byte size
Select Tablespace_name, SUM (BYTES)/1024/1024 as "Free SPACE (M)"
From Dba_free_space
WHERE tablespace_name = ' &tablespace_name '
GROUP by Tablespace_name;

Note: If this is a temporary tablespace, please check the Dba_temp_free_space
Select Tablespace_name, free_space/1024/1024 as "free SPACE (M)"
From Dba_temp_free_space
WHERE tablespace_name = ' &tablespace_name ';

2. Querying the tablespace for all data file paths
Select Tablespace_name, file_id, file_name, bytes/1024/1024 as "BYTES (M)"
From Dba_data_files
WHERE tablespace_name = ' &tablespace_name ';
Note: If this is a temporary tablespace, please check the Dba_temp_files
Select Tablespace_name, file_id, file_name, bytes/1024/1024 as "SPACE (M)"
From Dba_temp_files
WHERE tablespace_name = ' &tablespace_name ';

3. Add data files for space-deficient tablespaces
ALTER tablespace &tablespace_name ADD datafile ' &datafile_name ' SIZE 2G;
Note: If you are expanding the temporal tablespace, use the following statement
ALTER tablespace &tablespace_name ADD tempfile ' &datafile_name ' SIZE 2G;

4. View the size and data file path of the staging table space
Select Tablespace_name, file_id, file_name, bytes/1024/1024 as "SPACE (M)"
From Dba_temp_files
WHERE tablespace_name = ' TEMP ';
Or
Select name, bytes/1024/1024 as "size (M)" from V$tempfile Order by bytes;

5. Rebuild and modify the default temporary Tablespace method:
--Query the current database default temporary tablespace name
SELECT * from database_properties where property_name= ' default_temp_tablespace ';
--Create a new temporary table space
Create temporary tablespace temp02 tempfile ' E:\oracle\oradata\lims\TEMP02. DBF ' size 1024M autoextend on;
--Modify the default tablespace to the temporary table space just established
ALTER DATABASE default temporary tablespace temp02;
--View the temporary table space used by the user
SELECT username,temporary_tablespace from Dba_users;
--Delete the original temporary table space
Drop Tablespace temp including contents and datafiles;
--View all tablespace names to confirm that the staging table space has been deleted
Select Tablespace_name from Dba_tablespaces;

--Find Users
SELECT * from Dba_users;
--Find the path to the workspace
SELECT * from Dba_data_files;
--Delete User
Drop user name cascade;
--Delete Table space
Drop tablespace tablespace name including contents and datafiles cascade constraint;
--delete users, and cascade relationships are also removed
Drop user lYK cascade;
--Delete table space, and the corresponding tablespace file is also deleted
Drop tablespace lYK including contents and datafiles cascade constraint;

What is the difference between a DBA, a SYSDBA, and a system role?
Before I explain this, I need to talk about the process of creating Oracle Services
• Create an instance
• Launch Instances
• Create a database (System tablespace is required)
startup process
• Instance Launch
• Loading the database
• Open Database
SYSDBA, which manages Oracle instances, does not depend on the full start of the entire database,
As soon as the instance is started, it already exists, logs in as SYSDBA, loads the database, opens the database
The DBA role has the basis of existence only if the database is open, or when the entire database is fully booted, and the DBA is just a role.

Oracle-related operations, storage, temporary tablespace, user action, startup process