General operations in Oracle

First, modify the password:

Alter user test_user identified by password;

Second, modify the user default table space:

Alter user Test_user default tablespace users;

Third, modify user temporary table space:

Alter user Test_user Temporary tablespace temp;

Iv. Modify the configuration file:

Alter user Test_user profile Limit_file;

V. Modification of quotas:

Alter User Test_user quota 100M on users

Invalid use of account order:

Alter user test_user password expire;

Seven, lock the user:

Alter user Test_user account lock;

Viii. table Space Add data file:

Alter tablespace user_tbs Add datafile '/oracle/app/oradata/ora11g/datafile/tbs_kr.dbf ' size 100M;

Nine, modify the size of the data file:

Alter database DataFile

'/ORACLE/APP/ORADATA/ORA11G/DATAFILE/TBS_KR.DBF ' Resize 1024M

Ten, create a table space example:

Create tablespace tbs_dw Logging

DataFile '/opt/oracle/db/product/11.1/dbs/tbs_dw01.dbf ' size 20G

Reuse Autoextend on next 10M maxsize 30G

Extent management local segment spaces management auto;

Xi. Modify data File Growth Properties:

For data files that already exist, you can modify the properties of the data file as follows:

Alter database datafile '/opt/oracle/db/product/11.1/dbs/tbs_dw01.dbf '

Autoextend on

Next 10M

MaxSize Unlimited;

You can also specify the properties of a data file when you add a new data file to the tablespace:

Alter tablespace User_tbs Add datafile

'/ORACLE/APP/ORADATA/ORA11G/DATAFILE/TBS_KR.DBF ' size 100M

Autoextend on

Next 5M

MaxSize 1000M

12. Moving Data files

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Scenario: You can move data files from one disk to another by not having the available storage space on the disk where the data file resides.

The steps are as follows:

To convert a table space to a offline state:

A) Alter tablespace user_tbs offline;

(b) Manually copying data files to another disk;

Execute the following command:

Alter tablespace user_tbs rename datafile ' D:oracledatauser_tbs03. DBF '

To ' e:oradatauser_tbs03.dbf '

C Modify the status of the table space to online

Alter tablespace User_tbs Online;

13, the data file online, offline properties

Change data file mode: ALTER DATABASE datafile '/ORACLE/APP/ORADATA/ORA11G/DATAFILE/TBS_KR.DBF ' offline

Delete data file: Alter database datafile '/oracle/app/oradata/ora11g/datafile/tbs_kr.dbf ' offline drop

14, delete the table space

To delete tablespace and data file information in control files and data dictionaries:

Drop tablespace User_tbs including contents;

Delete tablespace information, data file information, and data files on disk:

Drop tablespace User_tbs including contents and datafiles;

Modify Table space Name:

Alter tablespace LBIDW Rename to Tbs_dw

Modification of temporary files, data file attributes examples

--1) Temporary data file

--View the self-growing properties of temporary files

Select A.file_name,a.tablespace_name,a.autoextensible from Dba_temp_files a where a.tablespace_name = ' TBS_BRAVO_TEMP '

--Modifying temporary files for self-growth

ALTER DATABASE Tempfile '/dev/raw/raw374 ' autoextend on;

--2) data File

--View the data file's self-growing properties

Select A.file_name,a.tablespace_name,a.autoextensible from Dba_data_files a where a.tablespace_name = ' TBS_BRAVO_DW '

--Modifying the data file's self-growing properties

ALTER DATABASE datafile '/dev/raw/raw189 ' autoextend on;