Directory:

Table space

What is a tablespace: Oracle users have their own default tablespace. Most of the memory stored in a tablespace is tables, so it is called a tablespace.

The user’s table space

Table Spaces for system users

2, common user tablespace

Why create your own tablespace for ordinary users?

The project is likely to use the same database as other projects,

It is possible for multiple users to access the same database file while using the same database,

You can create resource contention problems by assigning different table Spaces to different users

Use different data files to solve contention problems.

The user has a default tablespace, but the user can also use other tablespaces. If we do not specify the tablespace when creating an object, the object is stored in the default tablespace. To store the object in another tablespace, we need to specify the tablespace of the object when creating the object

Create table space syntax and give user table space

Prerequisites Open CMD, enter sqlPlus, connect to the database, and log in to the SYS.

Login sys:

sqlplus sys/123456@ZhengJiaAo:1521/ORCL as sysdba
Copy the code

0. View the tablespace names used by the administrator

Status Status (read-only READ ONLY/READ ONLINE)

SQL> select tablespace_name,status from dba_tablespaces; A permanent SYSTEM tablespace is used to store tables, views, and stored procedure objects for SYS users. SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE SYSAUX ONLINE UNDOTBS1 Mysql > create tablespace USERS ONLINE; mysql > create tablespace USERS ONLINECopy the code

1. Create temporary tablespace duke_temp

Create path I:\Orcl\ TABLESPACE

CREATE TEMPORARY TABLESPACE duke_temp /* TEMPORARY TABLESPACE name :duke_temp */ tempfile 'I:\Orcl\ TABLESPACE \duke_temp. DBF '/* TEMPORARY TABLESPACE path */ *-- Oracle file path */ size 50m /* 50M*/ autoextend ON /* Enable automatic expansion */ NEXT 50M /* When the space is full, 50M*/ maxSize 6 GB /* The final space size is 6 GB. The unlimited size can be set to unlimited */ extent management local; The tablespace has been created. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE TABLESPACE_NAME STATUS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DUKE_TEMP ONLINE / * / * newly created temporary table space selected 12 lines.Copy the code

2. Create tablespace duke_data

CREATE TABLESPACE duke_data /* TABLESPACE name :duke_data */ datafile 'I:\Orcl\ TABLESPACE duke_data. DBF '/* TABLESPACE path */ size 50m SQL > alter table size; 50M */ autoextend ON /* Enable automatic extension */ NEXT 50M /* NEXT 50M /* When the tablespace is full */ maxSize 10GB /* The final space size is 10GB, which can be set to Unlimited */ logging extent management local; The tablespace has been created. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE TABLESPACE_NAME STATUS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DUKE_TEMP ONLINE / * newly created temporary table space * / DUKE_DATA ONLINE /* Newly created tablespace */ 13 rows selected.Copy the code

3. Create a user and specify the tablespace

CREATE USER duke /* USER name */ IDENTIFIED BY duke /* password */ DEFAULT TABLESPACE duke_data /* TABLESPACE */ / specifies the DEFAULT TABLESPACE for the USER */ TEMPORARY TABLESPACE duke_temp /* temporary TABLESPACE */ * default temporary TABLESPACE */; A user has been created.Copy the code

4. Modify user default and temporary tablespaces

To facilitate future data backup and recovery, backing up only valuable data can improve efficiency and save space.

Alter tablespace default;

ALTER USER duke DEFAULT TABLESPACE USERS; /*duke user name, USERS DEFAULT tablespace */ user changed.Copy the code

Alter user temporary tablespace temp;

ALTER USER duke temporary TABLESPACE TEMP; /* Modify temporary tablespace */Copy the code

Note: Normal users do not have permission to modify the default tablespace, but they can also modify the default tablespace through authorization.

SQL> show user; SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMPCopy the code

5. Delete the tablespace table

—- Delete non-empty tablespaces, including physical files

DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES; The tablespace has been deleted.Copy the code

After a tablespace is deleted, the user that originally points to the tablespace still has the default tablespace location. You need to run the alter user command to point the user’s tablespace to a valid tablespace.

Mysql > delete tablespace tdb_name;

Drop TABLESPACE duke_temp -- Drop tablespace duke_temp including contents -- drop tablespace duke_temp; Drop TABLESPACE duke_temp including datafiles; DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES; -- If a table in another tablespace has a constraint such as a foreign key associated with a table in this tablespace, Duke_temp including contents and datafiles CASCADE CONSTRAINTS; Mysql > delete SCHEMA objects from schema duke; tablespace tablespace; Drop user duke cascade; - duke userCopy the code

6. Grant login and revoke permissions to the user

A newly created user cannot log in to the Oracle database. The user must be authorized to log in to the Oracle database by granting permissions to two built-in roles, connect and Resource.

The conncet role enables users to log in; The Resource role allows users to develop programs, such as creating tables, triggers, and so on.

Authorization command:

GRANT CONNECT,RESOURCE TO duke; Grant grant dba to oracle database */ grant grant dba to oracle database; /* Grant maximum dba privileges */Copy the code
Dike SQL> connect duke/duke SQL> show user; USER is "DUKE" SQL>Copy the code

7. Check the tablespace usage

SELECT UPPER(f.tablespace_name) AS "tablespace name ", ROUND(d.availb_bytes,2) AS" tablespace size (G)", ROUND(d.ax_bytes,2) AS "Final tablespace size (G)", ROUND((d.availb_bytes-f.used_bytes),2) AS" Used space (G)", TO_CHAR(ROUND((d.availb_bytes - f.used_bytes)/d.availb_bytes * 100, 2), '999.99') AS "Usage ratio ", ROUND(f.used_bytes, F. max_bytes AS "Max (M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024 * 1024 * 1024), 6) USED_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES, ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESCCopy the code

8. Check which tablespace common users belong to

SQL> select username,default_tablespace from dba_users where username='SCOTT'; / * USERNAME * / USERNAME DEFAULT_TABLESPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SCOTT USERSCopy the code

9. View all tablespaces

SQL> select * from v$tablespace; / * contains the table space from the control file names and Numbers information * / TS# NAME INC BIG FLA ENC -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 8 EBANK_TEMP NO NO YES 9 ECIF_DATA YES NO YES 10 ECIF_INDEX YES NO YES 11 EIP_DATA YES NO YES 13 YANG YES NO YES TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 14 DUKE_TEMP NO NO YES 15 DUKE_DATA YES NO YES 13 rows have been selected.Copy the code

10. View all users in the tablespace

SQL> select distinct s.owner from dba_segments s where s.tablespace_name ='USERS'; / * * all USERS under the USERS table space/OWNER -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SCOTT SYSTEM OECopy the code

11. Check which tablespace the current user’s table belongs to

SQL> select table_name,tablespace_name from user_all_tables; / * check if the current user belongs to which table space * / TABLE_NAME TABLESPACE_NAME -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- MLOG $ SYSTEM SLOG$ SYSTEM RGROUP$ SYSTEM RGCHILD$ SYSTEM SYS_IOT_OVER_5140 SYSAUX RULE_SET_PR$ SYS_IOT_OVER_5146 SYSAUX RULE_SET_IOT$ SYS_IOT_OVER_5150 SYSAUX RULE_SET_ROP$ SYS_IOT_OVER_5387 SYSAUXCopy the code

12. View the default and temporary tablespaces of the user

SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMPCopy the code

Default tablespaces

When a user creates a database object after login, if no tablespace is specified, the data is stored in the default tablespace.

13. System tablespace DBA_TABLespaces and user tablespace user_TABLespaces

A common user cannot view the system tablespace DBA_TABLespaces but only user tablespace user_tablespaces

SQL> connect duke/ DUKE is connected. SQL> show user; SQL> select TABLespACE_name from dBA_TABLespaces; Ora-00942 select TABLespACE_NAME from DBA_TABLespaces * SQL> SELECT TABLespACE_NAME from user_TABLespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE EBANK_TEMP ECIF_DATA ECIF_INDEX EIP_DATA YANG TABLESPACE_NAME -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DUKE_TEMP DUKE_DATA has chosen line 13.Copy the code

14. View the directory where the tablespace data files are stored:

Table space data files are stored in persistent table Spaces

SQL> desc dba_data_files; /* The data dictionary dba_data_file holds the attributes of the data file. */ Is the name empty? Type -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- FILE_NAME VARCHAR2 (513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL> select file_name from dba_data_files where tablespace_name='DUKE_DATA'; FILE_NAME -------------------------------------------------------------------------------- I:\ORCL\TABLESPACE\ duke_data. DBFCopy the code

Alter tablespace status (online/offline, read-only/read/write)

Make a tablespace ONLINE or OFFLINE: By default, the tablespace is ONLINE. If the tablespace is OFFLINE, the tablespace cannot be used.

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
Copy the code

For example, if the DUKE_DATA tablespace is offline, check the tablespace status

SQL> ALTER TABLESPACE duke_data OFFLINE; /* the DUKE_DATA tablespace is offline and needs to use larger letters */ the tablespace has changed. SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA'; /* Check tablespace STATUS */ STATUS --------- OFFLINECopy the code

Set the table space read-only | read-write state: the default table space to read write and read state

ALTER TABLESPACE duke_data read only|read write;
Copy the code

Note: If a table space has a readable state, the table space must be ONLINE.

SQL> ALTER TABLESPACE duke_data read only; Ora-01539: TABLESPACE 'duke_data' is not onlineCopy the code

Table space changed state to read-only after online

SQL> ALTER TABLESPACE duke_data ONLINE; The tablespace has changed. SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA'; --------- ONLINE SQL> ALTER TABLESPACE duke_data read only; The tablespace has changed. select tablespace_name,status from dba_tablespaces where tablespace_name='DUKE_DATA'; /* Check the status of the DUKE_DATA tablespace, read/write (ONLINE),DUKE_DATA uppercase */Copy the code

16. Modify the data files of the tablespace

Note: If a table space adds data files, the table space must be ONLINE.

Add the data file xx.dbf to the tablespace

SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA'; --------- ONLINE SQL> ALTER TABLESPACE duke_data ADD DATAFILE'I:\Orcl\ TABLESPACE \ duke_datA2.dbf ' SIZE 50m; /* Add datafile */ tablespace changed. SQL> select file_name from dba_data_fileS where tablespace_name='DUKE_DATA'; / * view the data file and the file location * / FILE_NAME -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- I:\ORCL\TABLESPACE\DUKE_DATA.DBF I:\ORCL\TABLESPACE\DUKE_DATA2.DBFCopy the code

Note: If the storage space of a table space is insufficient, you can add new data files to the table space to expand the size of the table space

SQL> ALTER TABLESPACE DUKE_DATA ADD DATAFILE 'I:\ORCL\TABLESPACE\DUKE_DATA03.dbf' SIZE 2G AUTOEXTEND OFF; /* Add a new big data file to the tablespace */ the tablespace has changed. SQL> ALTER TABLESPACE DUKE_DATA /* */ 2 ADD DATAFILE 'I:\ORCL\TABLESPACE\ duke_data04.dbf '3 SIZE 100M 4 AUTOEXTEND ON 5 NEXT 10M 6 MAXSIZE 20480M; The tablespace has changed.Copy the code

Note: When adding a new data file, the ALTER TABLESPACE statement will fail if the operating system of the same name already exists. Specifies the REUSE clause that must be displayed later if you want to overwrite an operating system file of the same name.

  • Resize data files
SQL> ALTER DATABASE DATAFILE 'I:\ORCL\TABLESPACE\DUKE_DATA04.dbf' RESIZE 500M; The database has changed.Copy the code
  • Deleting data files

Note: You cannot delete the first datafile in a tablespace. If you delete the first datafile, you delete the entire tablespace.

SQL> ALTER TABLESPACE DUKE_DATA DROP DATAFILE'I:\ORCL\TABLESPACE\DUKE_DATA04.dbf'; /* The tablespace has been changed.Copy the code

SQL> alter tablespace DUKE_DATA drop datafile 'I:\ORCL\TABLESPACE\DUKE_DATA03.dbf'; /* The tablespace has been changed.Copy the code

View the data files of the duke_data tablespace after the file is deleted

SQL> select file_name from dba_data_fileS where tablespace_name='DUKE_DATA';

FILE_NAME
--------------------------------------------------------------------------------
I:\ORCL\TABLESPACE\DUKE_DATA.DBF
I:\ORCL\TABLESPACE\DUKE_DATA2.DBF
Copy the code

17. Delete a tablespace

Basic syntax:

DROP TABLESPAC tablespace_name [INCLUDING CONTENTS];
Copy the code

Note: if you want to delete the data files in the tablespace at the same time, you need to add [INCLUDING CONTENTS].

drop tablespace DUKE_DATA including contents; /* Delete tablespace */ tablespace changed. select file_name from dba_data_fileS where tablespace_name='DUKE_DATA'; /* The DUKE_DATA tablespace does not exist.Copy the code

18. User tablespace quota

The table space storage limit is the total amount of storage space that a user can use in a particular table space. When a user is created or modified, it can be indicated by the quota parameter. If the user exceeds this limit when storing data into a table space, an error occurs.

Ora-01536: Space Quota exceeded for TABLESPACE TABLespacENAME.. ‘. You can query the dBA_ts_quotas dictionary to view the tablespace quota information.

View the tablespace quota of the user

SQL> SELECT * FROM DBA_TS_QUOTAS 2; TABLESPACE_NAME USERNAME BYTES ------------------------------ ------------------------------ ---------- MAX_BYTES BLOCKS  MAX_BLOCKS DRO ---------- ---------- ---------- --- SYSAUX APPQOSSYS 0 -1 0 -1 NO SYSAUX FLOWS_FILES 0 -1 0 -1 NO SYSAUX SYSMAN 160038912 -1 19536 -1 NO TABLESPACE_NAME USERNAME BYTES ------------------------------ ------------------------------ ---------- MAX_BYTES BLOCKS MAX_BLOCKS DRO ---------- ---------- ---------- --- SYSAUX OLAPSYS 4718592 -1 576 -1 NOCopy the code

View the current user tablespace quota

SELECT * FROM USER_TS_QUOTAS; SQL> DESC DBA_TS_QUOTAS; Is the name empty? Type -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- TABLESPACE_NAME NOT NULL VARCHAR2 (30)  USERNAME NOT NULL VARCHAR2(30) BYTES NUMBER MAX_BYTES NUMBER BLOCKS NUMBER MAX_BLOCKS NUMBER DROPPED VARCHAR2(3)Copy the code

Note: If MAX_BYTES=-1, there is no quota limit

Oracle database set read/write mode of the tablespace

Meaning: The main purpose is to ensure data integrity.

For example: data backup and restore operation, historical data integrity protection. Table Spaces can be set to read-only mode.

Read-only: Yes No INSERT, UPDATE, or DELETE operations can be performed in the tablespace.

You need to log in as a dba user to perform the following operations:

1. Open CMD, enter SQLPlus, connect to the database, and log in to sys.

sqlplus sys/123456@ZhengJiaAo:1521/ORCL as sysdba
Copy the code
SQL> select tablespace_name,status from dba_tablespaces; SQL > alter table tablespace status; Reading and writing (ONLINE) TABLESPACE_NAME STATUS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE 11 rows have been selected.Copy the code

2. Change the tablespace status: READ ONLY.

SQL> alter tablespace users read only; The tablespace has changed. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS READ ONLY EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE select 11 lines.Copy the code

3. Change the tablespace status to Read/write (ONLINE).

SQL> alter tablespace users read write; The tablespace has changed. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE EBANK_TEMP ONLINE ECIF_DATA ONLINE ECIF_INDEX ONLINE EIP_DATA ONLINE YANG ONLINE 11 lines have been selected.Copy the code

Schema and users

The difference between Oracle Schema and User

Schema definition:

A schema is a collection of database objects (used by a user). Schema objects are the logical structures that directly refer to the database’s data. A user is a name defined in the database that can connect to and access objects。 Schemas and users help database administrators manage database security

A schema is a collection of database objects (used by users).

A Schema object is a logical structure that directly references database data.

A user is the name of an object defined in the database that can be connected to and accessed.

Schemas and users help database administrators manage database security

A schema is a collection of database objects. In order to distinguish the collection, we need to give the collection a name. These names are the many user-name-based nodes that we see in the enterprise Manager schema. Schema contains objects such as tables, views, sequences, stored procedures, SYNONYMS, indexes, clusters, and database links.

1. A user generally corresponds to one schema:

Generally, a user corresponds to one schema. The schema name of the user is equal to the user name and serves as the default schema of the user. This is why you see schema names as database usernames in enterprise Manager scenarios.

Database Schema has two meanings. One is a conceptual Schema, which refers to a set of DDL statements that completely describe the structure of a database. There is also a physical Schema, which refers to a namespace in a database that contains a set of named objects such as tables, views, and stored procedures. Physical schemas can be created, updated, and modified using standard SQL statements. For example, the following SQL statement creates two physical schemas:

create schema SCHEMA_A; Create table SCHEMA_A.CUSTOMERS(ID int not null,......) ;Copy the code
create schema SCHEMA_B; Create table schema_b. CUSTOMERS(ID int not null,......) ;Copy the code

Simply put: a database object owned by a database user. For example, if Scott creates tables, indexes, views, stored procedures, etc., these objects form Schema Scott

You cannot create a schema in an Oracle database. To create a schema, you must create a user (Oracle has a create Schema statement, but it is not used to create a schema). When you create a user, create a schem with the same name as the user and use it as the default shcema for that user.

That is, the number of schemas is the same as the number of users, and the name of the schema is the same as the name of the user, so we can call the schema an alias for user, although it is not accurate, but it is easier to understand.

2. A user can also use other schemas:

When accessing a table without specifying which schema the table belongs to, the system automatically assigns the default Sheman name to the table. Select * from emp; select * from emp; Select * from scott.emp; The full name of an object in the database is schema.object, not user.object.

3. Oracle schema refers to a collection of all objects under a user:

Schema itself cannot be understood as an object. Oracle does not provide the syntax for creating a schema, and the schema is not created when a user is created. Instead, the schema is created after the first object is created under the user. If no object exists under user, the schema does not exist either. This is similar to the TEMP TABLESPACE group, and can also be observed through OEM. If a new user is created, if there is no object under the user, the schema does not exist. If an object is created, the schema with the same name as the user is also generated.