Basic Oracle operations

1. Create a table

Create Table Name (

Column name 1 type,

Column name 2 type

);

2. Modify class attributes

Alter table table name modify (column name type );

3. Add Columns

Alter table table name Add (column name type );

4. Add primary key constraints and non-empty Constraints

Alter table table name Add constraint pK _ table name primary key (column name );

Alter table table name modify (column name not null );

5. Delete the primary key constraint

Alter table Table Name drop primary key;

Alter table Table Name drop constraint pK _ table name;

6. Failure Constraint

Alter table table name disable primary key;

Alter table table name disable constraint pK _ table name;

7. Valid Constraints

Alter table table name enable primary key;

Alter table table name enable constraint pK _ table name;

8. Delete Columns

Alter table Table Name drop column name;

9. Set a column to unavailable and delete it.

Alter table table name set unused (column name );

Alter table Table Name drop unused columns;

10. Modify the table name

Rename table name 1 to table name 2

Alter table name 1 Rename to table name 2;

11. Table Truncation

Truncate table name;

12. truncate the table and retain row space

Truncate table name resue storage;

13. view the table structure

Desc table name;

14. delete a table

Drop table name;

15. Insert record

Example: insert into table name values (Content 1, content 2, content 3, content 4 );

16. Insert rows in dialog mode with Parameters

Example: insert into table name values (& Column name 1, & Column name 2 );

Insert into table name values (Content 1, content 2 );

17. Insert several columns of records

Insert into Table Name (column name 1, column name 2) values (Content 1, content 2 );

18. Insert a null value for the column (its column cannot be not null)

Insert into table name values (Content 1, null, null );

19. Create a table (including primary key and foreign key settings) method 1

Create Table Name (

Column name 1 Type

Constraint pK _ table name primary key,

Column name 2 type not null,

Column name 3 type

Constraint FK _ Table Name Reference Table Name (column name ),

Column name 3 type

Constraint CK _ table name check (column name 3 in (''content 1 '', ''content 2'', ''content 3 ''))

);

20. query all rows

Select * from table name;

21. query certain Columns

Select column name 1, column name 2 from table name;

22. Duplicate row Elimination

Select distict column name from table name;

23. Where statement Query

Select * from table name where condition order by column name;

(Note: If the number type is queried automatically in ascending order, if you want to sort it in descending order, select * from table name where condition order by column name DESC ;)

24. Create a table. method 2

Create Table Name (

Column name 1 type primary key,

Column name 2 type not null,

Check (column name 3 in ('''','''','''')),

Column name 4 type refernce table name (column name)

);

25. Modify the column = '? 'Data

Update table name set (column = ?) Where column = '? ';

26. Delete rows

Delete from table name where condition;

27. Transaction Processing

-- Transaction Processing

Update table name

Set column name (date) = ''30-May-98''

Where condition;

Savepoint mark1;

Delete from table name where condition;

Savepoint mark2;

Rollback to savepoint mark1;

Rollback;

28. Create user1 with Password

Grant connect and resource permissions to users

Connect role for Logon

The resource role is used to create tables.

Connect system/Manager

Create user user1 identified by password;

Grant connect, resource to password;

29. Data Control Language

Connect Scott/Tiger

30. Grant the permission to query and Modify Table 1 to user1.

Grant select, update on table 1 to user1;

31. Grant the permission to modify column 1 and column 2 in table 1 to user1.

Grant Update (column 1, column 2) on table 1 to user1;

32. Grant the permission to query Table 1 to user user1

In addition, user1 users can grant this permission to other users (with grant option)

Grant select on table 1 to user1 with grant option;

33. revoke the permission to query and Modify Table 1 from user user1

Revoke select, update on table 1 from user1;

34. Create a tablespace statement

Create tablespace tablespacename datafile filename size ** M online;

For example:

Create tablespace photo datafile 'e:/photo. dbf' size 1000 m online;

35. Modify Table space statements

Alter database datafile filename resize ** M online;

For example:

Alter database datafile 'e:/photo. dbf' resize 2000 m online;

36. Add additional data files to the tablespace

Alter tablespace tablespacename add datafile filename size ** m

For example:

Alter tablespace photo add datafile 'e:/photo2.dbf' size 2000 m

36. Modify the tablespace name

Alter tablespace oldtsname Rename to newtsname

37. view the table space name and size.

Select T. tablespace_name, round (sum (Bytes/(1024*1024), 0) ts_size

From dba_tablespaces T, dba_data_files d

Where T. tablespace_name = D. tablespace_name

Group by T. tablespace_name;

38. view the name and size of the tablespace physical file.

Select tablespace_name, file_id, file_name,

Round (Bytes/(1024*1024), 0) total_space

From dba_data_files

Order by tablespace_name;

39. view the rollback segment name and size

Select segment_name, tablespace_name, R. status,

(Initial_extent/1024) initialextent, (next_extent/1024) nextextent,

Max_extents, V. curext curextent

From dba_rollback_segs R, V $ rollstat v

Where R. segment_id = V. USN (+)

Order by segment_name;

40. View Control Files

Select name from V $ controlfile;

Select member from V $ logfile;

41. View table space usage

Select sum (bytes)/(1024*1024) as free_space, tablespace_name

From dba_free_space

Group by tablespace_name;

Select a. tablespace_name, A. bytes total, B. bytes used, C. bytes free,

(B. bytes * 100)/A. bytes "% used", (C. bytes * 100)/A. bytes "% free"

From SYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free C

Where a. tablespace_name = B. tablespace_name and A. tablespace_name = C. tablespace_name;

42. view database objects

Select owner, object_type, status, count (*) Count # From all_objects group by owner, object_type, status;

43. view the database version

Select version from product_component_version

Where substr (product, 1, 6) = 'oracle ';

44. view the database creation date and archiving method

Select created, log_mode, log_mode from V $ database;