Basic orcal database operations

1. Connection

SQL * Plus system/manager

2. display the current connected user

SQL> show user

3. view the users in the system

SQL> select * from all_users;

4. Create and authorize a user

SQL> create user a identified by a; (created in the SYSTEM tablespace by default)

SQL> grant connect, resource to;

5. connect to a new user

SQL> conn a/

6. query all objects under the current user

SQL> select * from tab;

7. Create the first table

SQL> create table a (a number );

8. query the table structure

SQL> desc

9. Insert a new record

SQL> insert into a values (1 );

10. query records

SQL> select * from;

11. change history

SQL> update a set a = 2;

12. delete records

SQL> delete from;

13. rollback

SQL> roll;

SQL> rollback;

14. Submit

SQL> commit;

User authorization:

Grant alter any index to "user_id"

GRANT "dba" TO "user_id ";

Alter user "user_id" DEFAULT ROLE ALL

Create a user:

Create user "user_id" PROFILE "DEFAULT" identified by "default tablespace" USERS "temporary tablespace" TEMP "account unlock;

GRANT "CONNECT" TO "user_id ";

User password settings:

Alter user "CMSDB" identified by "pass_word"

Create a tablespace:

Create tablespace "table_space" logging datafile 'C: \ ORACLE \ ORADATA \ dbs \ table_space.ora 'SIZE 5 M

------------------------------------------------------------------------

1. view all objects currently

SQL> select * from tab;

2. Create an empty table with the same structure as Table

SQL> create table B as select * from a where 1 = 2;

SQL> create table B (b1, b2, b3) as select a1, a2, a3 from a where 1 = 2;

3. Check the database size and space usage

SQL> col tablespace format a20

SQL> select B. file_id File ID,

B. tablespace_name tablespace,

B. file_name physical file name,

B. Total bytes,

(B. bytes-sum (nvl (a. bytes, 0) already in use,

Sum (nvl (a. bytes, 0) remaining,

Sum (nvl (a. bytes, 0)/(B. bytes) * 100 percentage remaining

From dba_free_space a, dba_data_files B

Where a. file_id = B. file_id

Group by B. tablespace_name, B. file_name, B. file_id, B. bytes

Order by B. tablespace_name

/

Dba_free_space -- the remaining space in the tablespace

Dba_data_files -- data file space usage

4. view existing rollback segments and their statuses

SQL> col segment format a30

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS FROM DBA_ROLLBACK_SEGS;

5. view the data file placement path

SQL> col file_name format a50

SQL> select tablespace_name, file_id, bytes/1024/1024, file_name from dba_data_files order by file_id;

6. display the current connected user

SQL> show user

7. Use SQL * Plus as a calculator

SQL> select 100*20 from dual;

8. connection string

SQL> select column 1 | Column 2 from table 1;

SQL> select concat (column 1, column 2) from table 1;

9. query the current date

SQL> select to_char (sysdate, 'yyyy-mm-dd, hh24: mi: ss') from dual;

10. Data replication between users

SQL> copy from user1 to user2 create table2 using select * from table1;

11. order by cannot be used in views, but it can be replaced by group by for sorting purposes.

SQL> create view a as select b1, b2 from B group by b1, b2;

12. Create a user through authorization

SQL> grant connect, resource to test identified by test;

SQL> conn test/test

13. Check all the table names of the current user.

Select unique tname from col;

-----------------------------------------------------------------------

Alter table alist_table add address varchar2 (100 );

Alter table alist_table modify address varchar2 (80 );

Create table alist_table_copy as select ID, NAME, PHONE, EMAIL,

QQ as QQ2,

ADDRESS from alist_table;

Drop table alist_table;

Rename alist_table_copy to alist_table

Null Value Processing

The column value cannot be empty.

Create table dept (deptno number (2) not null, dname char (14), loc char (13 ));

Add a column to the base table

Alter table dept

Add (headcnt number (3 ));

Modify attributes of an existing column

Alter table dept

Modify dname char (20 );

Note: The column value width can be reduced only when all values of a column are empty.

The column value type can be changed only when all values of a column are empty.

The column not null can be defined only when all values of a column are not null.

Example:

Alter table dept modify (loc char (12 ));

Alter table dept modify loc char (12 );

Alter table dept modify (dname char (13), loc char (12 ));

Search for undisconnected connections

Select process, osuser, username, machine, logon_time, SQL _text

From v $ session a, v $ sqltext B where a. SQL _address = B. address;

-----------------------------------------------------------------

1. The data dictionary View starting with USER _ contains information owned by the current USER and queries the table information owned by the current USER:

Select * from user_tables;

2. The data dictionary View starting with ALL _ contains information owned by ORACLE users,

Query Information about all tables owned or accessed by the user:

Select * from all_tables;

3. Generally, only the ORACLE database administrator can access the View starting with DBA:

Select * from dba_tables;

4. query ORACLE users:

Conn sys/change_on_install

Select * from dba_users;

Conn system/manager;

Select * from all_users;

5. Create a database user:

Create user user_name identified by password;

Grant connect to user_name;

Grant resource to user_name;

Authorization format: grant (permission) on tablename to username;

Delete a user (or table ):

Drop user (table) username (tablename) (cascade );

6. import data tables to the created users

Imp system/manager fromuser = FUSER_NAME TOUSER = USER_NAME FILE = C: \ EXPDAT. dmp commit = Y

7. Index

Create index [index_name] on [table_name] ("column_name ")