ORACLE_SHEET

export ORACLE_SID=ORC1
sqlplus / as sysdba

View Database information
	View the version of Oracle that is installed.

   SELECT * FROM PRODUCT_COMPONENT_VERSION;
    - or -
   SELECT * FROM V$VERSION;

2. View database name.

   SELECT NAME FROM V$DATABASE;
    - or -
   SELECT * FROM GLOBAL_NAME;

3. View NLS (National Language Support) Parameters

   SELECT * FROM NLS_DATABASE_PARAMETERS;
    - or -
   SELECT * FROM V$NLS_PARAMETERS;

4. View Sessions

   SELECT SCHEMANAME, OSUSER, MACHINE, PROGRAM, STATE FROM V$SESSION;

5. View Services

   SELECT SERVICE_ID, NAME, NETWORK_NAME FROM DBA_SERVICES;

6. View current database instance details

   SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS FROM V$INSTANCE;
   
   
   Managing Tablespaces and Data files
   
   List tablespaces, status and type

   SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

2. Create tablespace

   CREATE TABLESPACE myspace 
       DATAFILE 'datafile_directory_path\data_file_name.dbf' 
       SIZE 20M 
       AUTOEXTEND ON
       NEXT 512K
    MAXSIZE UNLIMITED;

3. List Datafiles, tablespaces and status

   SELECT FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_DATA_FILES;

4. To check the current size of a tablespace

   SELECT SUM(bytes/1024/1024/1024) "Size in GB" from DBA_DATA_FILES WHERE TABLESPACE_NAME='MYSPACE';

3. List Datafiles, tablespaces and status

   SELECT FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_DATA_FILES;

5. Check the size of a database

Size of an Oracle database is the sum of the size of its Data files, Temporary files, Redo logs and Control files.

   SELECT ROUND(
       SUM(Q1."Data Files" + 
           Q2."Temp Files" + 
           Q3."Redo Logs" + 
           Q4."Control Files"
           )/1024/1024/1024,  2) 
       AS "Total Size (GB)"
   FROM
    (SELECT SUM(bytes) "Data Files" from DBA_DATA_FILES) Q1,
    (SELECT SUM(bytes) "Temp Files" from DBA_TEMP_FILES) Q2,
    (SELECT SUM(bytes) "Redo Logs" from V_$LOG) Q3,
    (SELECT SUM(BLOCK_SIZE * FILE_SIZE_BLKS)"Control Files" FROM V$CONTROLFILE) Q4;


Managing Users and Security
List all users, account status and profile

   SELECT USERNAME, ACCOUNT_STATUS, PROFILE FROM DBA_USERS;

2. List all roles

   SELECT * FROM DBA_ROLES;

3. Create User

   CREATE USER charlie IDENTIFIED BY password123;

Note: Two administrative user accounts SYS and SYSTEM are created by default. Default password for SYS user is CHANGE_ON_INSTALL and SYSTEM user is MANAGER
4. Change user password

   ALTER USER charlie IDENTIFIED BY newpassword;
    - or -
   PASSWORD

5. Create user profile (with all default limits)

   CREATE PROFILE MY_PROFILE LIMIT;

6. View all user profiles and limits

   SELECT * FROM DBA_PROFILES;
   SELECT * FROM DBA_PROFILES WHERE PROFILE='MY_PROFILE';

7. Change password lifetime, reuse time, failed login attempts

   SELECT * FROM DBA_PROFILES WHERE PROFILE='MY_PROFILE' AND RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

8. Set password expiry

To set password to 60 days for example:

   ALTER PROFILE MY_NEW_PROFILE LIMIT PASSWORD_LIFE_TIME 60;

To set password to never expire:

   ALTER PROFILE MY_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

9. View privileges granted to a user on other users tables

   SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='USERNAME';

10. View all user privileges including the privileges that are indirectly granted through roles

   SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='USERNAME' or GRANTEE in (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='USERNAME');



To get the list of databases
SELECT username FROM all_users ORDER BY username;

To get the current schema
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

 To get the list of tables within the current database
 select * from user_tables;
	  
To get the connected connection info
SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';

TO limit the selection
select * from table_name where ROWNUM <= 10;

 To view errors/warnings
select * from user_errors;/ show errors

Create table without the auto_increment keywords (because it does not exist in Oracle)

create table table_name (element_id int primary, element_name varchar(20));

Create a sequence, which provides the incremented values

create sequence auto_incrementor;

Create a trigger, which gets the next value from the sequence and updates it to the column to be auto_incremented

    CREATE TRIGGER trig_incrementor BEFORE INSERT ON table_name
    FOR EACH ROW
    BEGIN
    SELECT auto_incrementer.NEXTVAL into :new.element_id FROM dual;
    END;