Scribd
Upload
66 views

Listener: LSNRCTL Start LSNRCTL Stop: Alter Database Mount Alter Database Open

This document provides examples of SQL commands and statements for managing Oracle databases including: - Starting up, opening, mounting, and shutting down databases - Viewing tablespaces and data files - Creating, altering, and dropping users, roles, profiles, and database links - Granting and revoking privileges - Setting default tablespaces and roles

Uploaded by

Behram TURAN
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
66 views

Listener: LSNRCTL Start LSNRCTL Stop: Alter Database Mount Alter Database Open

This document provides examples of SQL commands and statements for managing Oracle databases including: - Starting up, opening, mounting, and shutting down databases - Viewing tablespaces and data files - Creating, altering, and dropping users, roles, profiles, and database links - Granting and revoking privileges - Setting default tablespaces and roles

Uploaded by

Behram TURAN
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

sqlplus / as sysdba

STARTUP;

STARTUP NOMOUNT;

ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE MOUNT STANDBY DATABASE;

SHUTDOWN IMMEDIATE;

listener: lsnrctl start lsnrctl stop

SQL> SELECT instance_name, status FROM v$instance;


INSTANCE_NAME STATUS
---------------- ------------
ORCL OPEN

SHUTDOWN IMMEDIATE; /ABORT;

Default tablespaces in Oracle

Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

ALTER TABLESPACE oradatatablespace READ ONLY;

select * from dba_data_files; (view)

SELECT tablespace_name, file_name, bytes / 1024 / 1024 MB FROM dba_data_files;

CREATE TABLE SCOTT.adana


( ID INTEGER DEFAULT NOT NULL,
NAME VARCHAR2 (200)
);
ALTER TABLE SCOTT.adana
ADD (CONSTRAINT adana_PK PRIMARY KEY (ID) ENABLE VALIDATE);

DROP TABLESPACE tablespace_name


[INCLUDING CONTENTS [AND | KEEP] DATAFILES]
[CASCADE CONSTRAINTS];

DROP TABLESPACE tbs3 INCLUDING CONTENTS;

DROP TABLESPACE tbs3 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

### EXTEND TABLESPACE DATAFİLE


ALTER DATABASE
DATAFILE 'tbs11.dbf'
RESIZE 15m;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_denemeta_hwxt7o6w_.dbf'

RESIZE 110M;

Database Özelliklerini sorguda görebiliriz

SELECT property_name, property_value, DESCRIPTION FROM


database_properties;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To find the current default temporary tablespace, you execute the following
statement:

SELECT
property_name,
property_value
FROM
database_properties
WHERE
property_name='DEFAULT_TEMP_TABLESPACE';
Here is the output:

SELECT * FROM dba_temp_free_space;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

CREATE TEMPORARY TABLESPACE temp2


TEMPFILE 'temp2.dbf'
SIZE 100m;

Oracle tablespace group examples


First, create a new temporary tablespace and assign it to the tablespace group tbs1:
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'temp2.dbf'
SIZE 100M
TABLESPACE GROUP tbsg1;
Because the tablespace group tbsg1 has not existed, the statement also created the
tablespace group tbsg1.
Second, assign the temp temporary tablespace temp to the tbsg1 tablespace group:
ALTER TABLESPACE temp TABLESPACE GROUP tbsg1;
Third, assign the tablespace group gbsg1 as the default temporary tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbsg1;
Finally, verify the current default temporary tablespace:

SELECT
property_name,
property_value
FROM
database_properties
WHERE
property_name='DEFAULT_TEMP_TABLESPACE';

CREATE USER john IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO john;

GRANT CREATE TABLE TO john;


GRANT SELECT, INSERT, UPDATE, DELETE ON ot.customers TO bob;
SELECT * FROM session_privs;
ALTER USER john QUOTA UNLIMITED ON USERS;

***To revoke all system privileges from a user, you can use the following statement:
REVOKE ALL PRIVILEGES FROM user;

REVOKE CREATE SESSION FROM bob;


REVOKE CREATE TABLE FROM bob;
REVOKE SELECT, INSERT, UPDATE, DELETE ON ot.customers FROM bob;

ALTER USER dolphin ACCOUNT LOCK;


ALTER USER dolphin ACCOUNT UNLOCK;

***To set the password of the user dolphin expired, you use the following statement:
ALTER USER dolphin PASSWORD EXPIRE;

SELECT
username,
profile
FROM
dba_users
WHERE
username ='DOLPHIN';

****When you create a new user without specifying a profile, Oracle will assign


the DEFAULT profile to the user.
Let’s create a new user profile called ocean:
CREATE PROFILE ocean LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 60;

and assign it to the user dolphin:


ALTER USER dolphin PROFILE ocean;

USER ROLES;

CREATE ROLES rescue;


GRANT CREATE TABLE, CREATE VIEW TO rescue;
SELECT * FROM session_roles;
GRANT rescue TO dolphin;

CREATE ROLE super;


GRANT ALL PRIVILEGES TO super;
GRANT super TO dolphin;
ALTER USER dolphin DEFAULT ROLE super;

DROP USER
If you specify the CASCADE option, Oracle will remove all schema objects of the user
before deleting the user.
If the schema objects of the dropped user are referenced by objects in other
schemas, Oracle will invalidate these objects after deleting the user.

If a table of the dropped user is referenced by materialized views in other schemas,


Oracle will not drop these materialized views. However, the materialized views can no
longer be refreshed because the base table doesn’t exist anymore.

Note that Oracle does not drop roles created by the user even after it deletes the user.

DROP USER bar;


DROP USER bar CASCADE;

SELECT * FROM session_privs ORDER BY privilege;

Grant SELECT on all tables in a schema to a user


CREATE PROCEDURE grant_select(
username VARCHAR2,
grantee VARCHAR2)
AS
BEGIN
FOR r IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = username
)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
END LOOP;
END;

EXEC grant_select('OT','DW');

List all users that are visible to the current user:


SELECT * FROM all_users;
List all users in the Oracle Database:
SELECT * FROM dba_users;
Show the information of the current user:
SELECT * FROM user_users;

CREATE ROLES mdm;


SET ROLE mdm;
SELECT * FROM session_roles;
CREATE ROLE order_entry IDENTIFIED BY xyz123;
ALTER ROLE db_designer NOT IDENTIFIED;
DROP ROLE role_name;

It is possible to enable multiple roles at once like the following statement:

SET ROLE role1, role2, ...;


Or
SET ROLE
role1,
role2 IDENTIFIED BY password,
...;
The following query returns the roles granted to the user michael:
SELECT *
FROM dba_role_privs
WHERE grantee = 'MICHAEL';

Oracle CREATE PROFILE


CREATE PROFILE profile_name
LIMIT { resource_parameters | password_parameters};

CREATE PROFILE CRM_USERS LIMIT


SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 15;

CREATE PROFILE erp_users LIMIT


FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90;

SELECT table_name FROM all_tables ORDER BY table_name;

SELECT table_name FROM user_tables ORDER BY table_name;


select status, database_status from v$instance;

Oracle CREATE DATABASE LINK


SELECT * FROM remote_table@database_link;
CREATE SYNONYM local_table FOR remote_table@database_link;
SELECT * FROM local_table;
CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';
ALTER DATABASE LINK private_dblink
CONNECT TO remote_user IDENTIFIED BY new_password;

ALTER PUBLIC DATABASE LINK public_dblink


CONNECT TO remote_user IDENTIFIED BY new_password;

DROP DATABASE LINK dblink;


DROP PUBLIC DATABASE LINK dblink;

You might also like