preface

In terms of traditional relational databases, oracle’s account and partition functions are amazing compared to mysql. In a word, absolutely!

1 Basic database configuration

Table space creation, user creation, user authorization

-- create tablespace TBS_DEMO -- name of the physical file in the tablespace datafile 'TBS_DEMO. DBF '-- 500M in size. Size 500M autoextend on next 5M maxSIZE Unlimited; -- Create user create user TEST -- Set password IDENTIFIED BY TEST -- Specify the default tablespace default TABLESPACE TBS_DEMO -- specify the temporary tablespace TEMP profile DEFAULT; -- Password expire; -- Password expire; Grant connect,resource,dba to TEST;Copy the code

1.1 the table space

1.2 user

1.3 permissions

2 Data Analysis

2.1 water level of the oracle

2.1.1 Viewing Water Levels

Full amount check

select * from dba_tables
Copy the code

Specify users and tables

Select * from dba_tables where owner = 'table_name' and table_name = 'table_name'Copy the code

Blocks = all blocks allocated, empty_blocks = unused blocks

View the actual data blocks used by the table

Select count (distinct dbms_rowid. Rowid_block_number (rowid) | | dbms_rowid. Rowid_relative_fno (rowid) zs from the name of the table;Copy the code

2.1.2 How Do I Lower the Water Level?

Create a new table from the specified result (a backup of the old table) Delete the original table Change the name of the new table to the original table

2.2 Analysis of Storage Space Occupied by Accounts/Libraries

2.2.1 Querying the Storage Space Occupied by Data

2.2.2 Viewing and emptying the recycle bin

3 Data import and export

3.1 Navicate Import and export data

3.2 Importing and Exporting dump Files

4 advanced SQL