Explain how to create and manage users in Oracle

# Valid Oracle identifiers

1. It cannot be a reserved keyword.

2. It must contain 1 to 30 characters. The database name cannot exceed 8 characters.

3. It must start with a letter in the database Character Set

4. Only letters in the database character set and the following characters can be contained: #, $, and _. In addition, the database link can contain the @ symbol and '.' (periods)

5. quotation marks are not allowed.

# Create a user

Create user angeos identified by angeos;

Set the user name to angeos and the password to angeos.

# User authorization

Grant connect, resource to angeos;

The angeos user is granted the permission to connect to the database and access resources.

# User authorization

Grant create session, dba to angeos;

Create session is a system privilege that provides users with the ability to connect to databases.

DBA is a role with over 120 SYSTEM privileges, So it allows users to perform almost any work in the database.

# Changing a user's password

Alter user angeos identified by oracle; change the password of user angeos to: oracle.

# Locking and unlocking an account

Alter user oe account unlock;

Then use user oe to log on to the database server with the password oe. Note: After the user is unlocked, restart the service.

# Modifying tablespace settings

By default, it uses the tablespace SYSTEM and TEMP (used to store temporary data ).
This method is not recommended. Therefore, we need to change the tablespace.

Connect to the database server through a system user

conn sys/sysadmin@hostname_services as sysdba;

View the tablespace

select tablespace_name,contents from dba_tablespaces order by tablespace_name;

Use USER tablespace instead of SYSTEM tablespace

alter user angeos default tablespace users temporary tablespace temp;

Try to create a table

create table table1 ( fieldA varchar2(10) )

# Deleting a user

Angeos

Drop user angeos;

Because angeos has a table table1, You need to specify the keyword CASCADE When deleting a user.

Drop user angeos cascade;

# Interpreting the term Schema

The schema of a database is defined as a set of database objects, and the schema name is the user name that owns or controls the set of database objects.
All database objects, such as tables, views, indexes, triggers, Java stored procedures, PL/SQL packages, and functions
Owned by a user in the database. Even Oracle data dictionaries and system catalogs are part of the SYS schema.

The traditional interpretation of a user is to uniquely identify the name and password combination of a set of trust creden.

# Explain SYSTEM privileges

There are two types of privileges in Oracle databases:

(1) object-level privileges are the privileges granted by users to access or operate on database objects.

(2) SYSTEM privileges are not used to control access to specified database objects, but to permit access to various features or to permit specific tasks in the Oracle database.

Query privilege

Connect system/sysadmin

Desc dba_sys_privs;

Query

Select distinct privilege from dba_sys_privs order by privilege

Statements that grant system privileges

GRANT system_priviege to username [with admin option];

Revoke SYSTEM privileges

REVOKE system_priviege from username;

Note: before any user can connect to the Oracle database, they must be given the create session privilege to provide them with a connection license.

##################################
# Creating a data table
##################################
# Syntax rules
Create table [SCHEMA.]
  
   
(
   

    
    
     
[Default
     
      
] [
      
        ]
       
[,
        
        
          [Default
         
           ] [
          
            ]
           
[,...]
           
);
          
         
        
       
      
     
    
   
  
# Example of table Creation
**************************************** ****************************
Create table CD_COLLECTION (
ALBUM_TITLE VARCHAR2 (100 ),
ARTISTVARCHAR2 (100 ),
COUNTRYVARCHAR2 (25 ),
RELEASE_DATE DATE,
LABELVARCHAR2 (25 ),
Primary key (ALBUM_TITLE, ARTIST)
);
**************************************** ****************************
Create table seagal. SONGS (
SONG_TITLEVARCHAR2 (100 ),
COMPOSER VARCHAR2 (100 ),
LENGTHNUMBER,
TRACKNUMBER,
ARTISTVARCHAR2 (100 ),
ALBUM_TITLE VARCHAR2 (100 ),
Foreign key (ARTIST, ALBUM_TITLE) REFERENCES seagal. CD_COLLECTION (ARTIST, ALBUM_TITLE ),
Primary key (SONG_TITLE, ARTIST, ALBUM_TITLE)
);

**************************************** ****************************
Select * from seagal. cd_collection;
Select * from seagal. songs;
**************************************** ****************************
**************************************** ****************************
Insert into seagal. CD_COLLECTION
VALUES ('black Sheets of rain', 'Bob mod', 'usa', to_date ('01-01-92 ', 'dd-MM-YY'), 'virgin ');
Insert into seagal. CD_COLLECTION
VALUES ('candy Apple grey', 'husker du', 'usa', to_date ('2017/86', 'dd/MM/yy'), 'Warner Brothers ');
Insert into seagal. SONGS
VALUES ('black Sheets of rain', 'cloud', NULL, 1, 'Bob cloud', 'black Sheets of rain ');
Insert into seagal. SONGS
VALUES ('crystal ', 'mod', 3.28, 1, 'husker du', 'candy Apple grey ');
Insert into seagal. SONGS
VALUES ('don'' t want to know if you are lonely ', 'hart', 3.28, 2, 'husker du', 'candy Apple grey ');
Insert into seagal. SONGS
VALUES ('I don't know for sure', 'mod', 3.28, 3, 'husker du', 'candy Apple grey ');
Insert into songs values ('black Sheets of rain', 'cloud', NULL, 1, 'Bob cloud', 'black Sheets of rain ');
COMMIT;
**************************************** ***************
**************************************** **************
Drop table fruits;
Create table fruits (
FRUIT VARCHAR2 (12 ),
COLOR VARCHAR2 (12 ),
Quantity number,
Price number,
PICKED DATE
);
Insert into fruits values ('apple', 'green', 12, 0.5, '12-Sep-2002 ');
Insert into fruits values ('apple', 'red', 12, 0.5, '15-Sep-2002 ');
Insert into fruits values ('mango', 'yellow', 10, 1.5,
'22-Sep-2002 ');
Insert into fruits values ('mangosteen', 'purple ', 5, 2,
'25-Sep-2002 ');
Insert into fruits values ('durian ', 'null', 2, 15, NULL );
Insert into fruits values ('Orange ', 'Orange', 10, 1.5, '28-Aug-2002 ');
COMMIT;
**************************************** *****************
COMMIT;

# Create table as select statement

You can create a table by querying a table and materialized the query result set into a regular table.
Copy the table structure, but the constraints, indexes, triggers, and other objects are not put into the new table.

Example:

Create table emp_copy

Select * from scott. emp;

# Data Dictionary

Each database has a data dictionary. any user who manages an Oracle database or uses Oracle to build an application must use the data dictionary.
A data dictionary is a catalog of Oracle databases.

When users, tables, constraints, and other database objects are created, Oracle automatically maintains a catalog of items stored in the database.

For example, the USER_TABLES view displays information about all tables owned by the current user.

Use the DESCRIBE command to view the structure information of the USER_TABLES view.

Users with DBA permissions can view DBA_TABLES

select owner ,table_name,tablespace_name from dba_tables where owner in('SCOTT','HR') order by owner,tablesapce_name,table_name

1. Explain how to process bad Oracle 9i data blocks
2. Analysis of Oracle Database management scripts
3. Auto-tuning in Oracle 11g