Create a tablespace in oracle

Create a tablespace in oracle

Four steps: describes how to create a tablespace.

Note:
1. If you open it in PL/SQL or other tools, directly modify the [italic bold part] in the following code to execute
2. Make sure that the path exists, for example, [D: \ oracle \ oradata \ Oracle9i \], that is, the path of the file to be saved exists.
/* Divided into four steps */
/* Step 2: create a temporary tablespace */

Copy the Code as follows:
Create temporary tablespace user_temp
Tempfile 'd: \ oracle \ oradata \ Oracle9i \ user_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;

/* Step 2: create a data table space */

Copy the Code as follows:
Create tablespace user_data
Logging
Datafile 'd: \ oracle \ oradata \ Oracle9i \ user_data.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local;

/* Step 2: create a user and specify the tablespace */

Copy the Code as follows:
Create user username identified by password
Default tablespace user_data
Temporary tablespace user_temp;

/* Step 4: grant permissions to users */

Copy the Code as follows:
Grant connect, resource, dba to username; view the physical location of the tablespace select file_name, tablespace_name from dba_data_files; Delete temporary tablespace: drop tablespace user_temp including contents and datafiles; Delete the tablespace:

For a single user and tablespace, you can use the following command.

 Step 1: delete a user

Drop user ××cascade

Note: If a user is deleted, the schema objects under the user is deleted, and the corresponding tablespace is not deleted.

Step 2: delete tablespace

Drop tablespace tablespace_name including contents and datafiles;

 

 

However, because the database is used by the development environment, many user and table spaces need to be cleared.

Ideas:

Export all the user and tablespace in the database, filter out the system and useful tablespace, and load useful information to a table.

Then write a routine loop to delete the tablespace that is not in use in the table.

1. select username, default_tablespace from dba_users;

2.

Create table MTUSEFULSPACE
(
ID Number (4) not null primary key,
USERNAME varchar2 (30 ),
TABLESPACENAME varchar2 (60 ),
OWNERNAME varchar2 (30)
);

3.

Declare icount number (2 );
Tempspace varchar2 (60 );
Begin
For curTable in (select username as allusr, default_tablespace as alltblspace from dba_users)
Loop
Tempspace: = curTable. alltblspace;
Dbms_output.put_line (tempspace );
Select count (TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
If icount = 0 then
Drop tablespace tempspace including contents and datafiles;
End if;
Commit;
End loop;
End;

 

The following error will be reported after execution

ORA-06550: 10th rows, 5th columns:
PLS-00103: The symbol "DROP" appears when one of the following is required:
Begin case declare exit
For goto if loop mod null pragma raise return select update
While with <an identifier>
<A double-quoted delimited-identifier> <a bind variable> <
Close current delete fetch lock insert open rollback
Savepoint set SQL execute commit forall merge pipe
06550. 00000-"line % s, column % s: \ n % s"
* Cause: Usually a PL/SQL compilation error.
* Action:

 

It seems that it is locked ..

 

 

No way. If the routines cannot be written, they can only group out statements for execution.

Export the user and tablespace to Excel. Use the CONCATENATE group to output the SQL statement.

Paste it to SQLdevelop for batch execution.

 

The entire deletion takes about 12 hours. It takes more than 100 users.

 

 

To find the specific location of datafile, you can use

Select t1.name, t2.name from v $ tablespace t1, v $ datafile t2 where t1.ts # = t2.ts #;

 

SQL code
-- Delete empty tablespace, but does not contain physical files
Drop tablespace tablespace_name;
-- Delete non-empty tablespace, but does not contain physical files
Drop tablespace tablespace_name including contents;
-- Delete empty tablespace, including physical files
Drop tablespace tablespace_name including datafiles;
-- Delete non-empty tablespace, including physical files
Drop tablespace tablespace_name including contents and datafiles;
-- If the tables in other tablespaces have foreign keys and other CONSTRAINTS associated with the table fields in the current tablespace, cascade constraints must be added.
Drop tablespace tablespace_name including contents and datafiles cascade constraints;