Knowledge points required for Oracle Database Management DBAs

Grantselectanydictionarytoscott; createtablet1asselect * fromemp; insertintot1select * fromt1; -- query the user to view scot

Grant select any dictionary to scott; create table t1 as select * from emp; insert into t1 select * from t1; -- query the user to view scot

Grant select any dictionary to scott;
Create table t1 as select * from emp;
Insert into t1 select * from t1;
-- Check the storage partition records whose segments are T1 under scott.
Select segment_name, extent_id, file_id, block_id, blocks
From dba_extents where owner = 'Scott 'and segment_name = 't1 ';
-- Allocate a storage interval of KB to segment T1
Alter table t1 allocate
Extent (datafile '/u01/app/Oracle/oradata/orcl/users01.dbf' size 100 k );
-- Reclaim the free space after the high water level line
Alter table t1 deallocate unused;
-- Reclaim the free space after 20 K of the high water level line
Alter table a deallocate unused keep 20 k;
SQL> truncate table T1;
After the table is truncated, the first partition of the segment still exists, but the data has been cleared.

Rename the data file name in oracle
SQL> alter tablespace aaa offline;
Tablespace altered.
SQL> select ts #, name from v $ tablespace;
TS # NAME
----------------------------------------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
4 USERS
3 TEMP
6 EXAMPLE
7 YUANLEI
8 AAA
SQL> select ts #, file #, name, status from v $ datafile;
TS # FILE # NAME STATUS
------------------------------------------------------------------------
0 1/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
1 2/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
2 3/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 4/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
6 5/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
8 6/u01/app/oracle/oradata/orcl/bbb01.dbf OFFLINE
SQL> host rename/u01/app/oracle/oradata/orcl/bbb01.dbf aaa01.dbf;
[Oracle @ oracle11gR2 orcl] $ pwd
/U01/app/oracle/oradata/orcl

[Oracle @ oracle11gR2 orcl] $ cp bbb01.dbf aaa01.dbf
[Oracle @ oracle11gR2 orcl] $ ls
Aaa01.dbf example01.dbf redo03.log temp01.dbf yuanlei01.dbf
Bbb01.dbf redo01.log sysaux01.dbf undotbs01.dbf
Control01.ctl redo02.log system01.dbf users01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/bbb01.dbf' to '/u01/app/oracle/oradata/orcl/aaa01.dbf ';
Database altered.

SQL> alter tablespace aaa online;
Tablespace altered.

SQL> select ts #, file #, name, status from v $ datafile;
TS # FILE # NAME STATUS
------------------------------------------------------------------------
0 1/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
1 2/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
2 3/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 4/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
6 5/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
8 6/u01/app/oracle/oradata/orcl/aaa01.dbf ONLINE
6 rows selected.
Renamed

----- Create temporary tablespace
SQL> create temporary tablespace test_temp
Tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10 M
Autoextend on next 10 M maxsize 100 M extent management local;

------ Create a user tablespace and create a user tablespace
SQL> create temporary tablespace test_temp tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10 M autoextend on next 10 M maxsize 100 M extent management local;
Tablespace created.
SQL> create tablespace test_data logging datafile '/u01/app/oracle/oradata/orcl/test_data.dbf'
2 size 10 M autoextend on next 20 M maxsize 100 M extent management local;
Tablespace created.
SQL> create user yuanlei identified by leiyuan default tablespace test_data temporary tablespace test_temp;
User created.

------ View all users
SELECT * FROM DBA_USERS;

----- View the user's default and temporary tablespace, followed by the where Condition
SQL> select username, default_tablespace, temporary_tablespace from dba_users;

----- Modify the user's default and temporary tablespace
SQL> alter user yuanlei default tablespace users;
User altered.
SQL> alter user yuanlei temporary tablespace temp;
User altered.