Oracle11g basic query notes ......

PS: updating ......

Create user: User tablespace and temporary tablespace can be specified

create user myuser identified by 123456default tablespace mytabletemporary tablespace tmp_table;

User authorization: You can select permissions as needed.

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO myuser;

Query all users:

select   *   from   all_users;

----------------------------------------------

View the table of the current user:

select table_name from user_tables;

View table structure: DESC table name;

View All table names (including system tables ):

select table_name from all_tables;

Check the number of tablespaces in the Oracle database:

SELECT COUNT(*) FROM DBA_DATA_FILES;

View the tablespace Name:

SELECT TABLESPACE_NAME FROM DBA_DATA_FILES;

View table space details:

SELECT FILE_NAME,TABLESPACE_NAME,BYTES,USER_BLOCKS FROM DBA_DATA_FILES;

Create a tablespace:

create tablespace tablespacenameloggingdatafile 'E:/app/user/oradata/orcl/tablespacename.dbf'Size 32mautoextend onextent management local;

Create temporary tablespace:

create temporary tablespace tablespacename tempfile 'E:/app/user/oradata/orcl/tablespacename.dbf'Size 32mautoextend onextent management local;

PS: differences between temporary tablespace and tablespace

Temporary space, as the name suggests, is used to execute an algorithm that contains sorting, and is released. Tablespace: the logical region of the database, which persists in the database and will not be lost automatically.

Creating a tablespace of the same size is much slower than a temporary tablespace! You can try ~

SQL> set timing onsql> Create tablespace data1 datafile 'd: data1.dbf 'size 1 GB; tablespace created. Used time: 00: 00: 38.73sql> create temporary tablespace data2 tempfile 'd: data2.dbf 'size 1 GB; tablespace created. Used time: 00: 00: 00.62sql>

The user's tablespace is the tablespace that will be placed by default when the user does not display the tablespace of the specified object storage when creating the database object. However, you can create database objects in any tablespace that is allowed to access the database.
Database objects created by the user will be stored in this tablespace. When creating a user, you must use the quota clause to allocate space quotas to the user in the default tablespace. If the default tablespace is not specified, oracle uses the system tablespace as the user's default tablespace, which should be avoided.
Temporary tablespace when creating a user:
When the SQL statements you run need to be sorted, a certain amount of temporary space is required. At this time, Oracle will create a temporary period in the user's temporary tablespace. This temporary segment belongs to the Sys user, not to the user!

The user's temporary table space is mainly the temporary sorting space that the user uses when executing a large SQL statement. If the temporary tablespace is insufficient, the hard disk is used for sorting, this results in a large number of disk read/write operations, thus affecting SQL Execution performance.