Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- - connect: connects to a database
- - disconnect: logs off but does not exit
- - exit: exists
- / as sysdba
- SQL> create user johny identified by 1234;
- SQL> select username from dba_users;
- ERROR:
- ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied
- SQL> grant create session to johny;
- username: johny
- password: 1234
- SQL> drop user johny;
- SQL> select tablespace_name from dba_tablespaces;
- SQL> create tablespace johny_tabspace
- 2 datafile 'johny_tabspace.dat'
- 3 size 10M autoextend on;
- SQL> create temporary tablespace johny_tabspace_temp
- 2 tempfile 'johny_tabspace_temp.dat'
- 3 size 5M autoextend on;
- SQL> create user johny
- 2 identified by 1234
- 3 default tablespace johny_tabspace
- 4 temporary tablespace johny_tabspace_temp;
- SQL> grant create session to johny;
- SQL> grant create table to johny;
- SQL> grant unlimited tablespace to johny;
- SQL> select * from session_privs;
- PRIVILEGE
- ----------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- SQL> create table johny_table
- 2 (
- 3 id int not null,
- 4 text varchar2(1000),
- 5 primary key (id)
- 6 );
- SQL> insert into johny_table (id, text)
- 2 values (1, 'This is some text.');
- SQL> select * from johny_table;
- ID TEXT
- --------------------------
- 1 This is some text.
- SQL> set pagesize 0
- SQL> set long 90000
- SQL> set feedback off
- SQL> set echo off
- SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
- CREATE TABLE "JOHNY"."JOHNY_TABLE"
- ( "ID" NUMBER(*,0) NOT NULL ENABLE,
- "TEXT" VARCHAR2(1000),
- PRIMARY KEY ("ID")
- USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
- FAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "JOHNY_TABSPACE" ENABLE
- ) SEGMENT CREATION IMMEDIATE
- PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
- FAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "JOHNY_TABSPACE"
- SQL> set pagesize 0
- SQL> set long 90000
- SQL> set feedback off
- SQL> set echo off
- SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
- CREATE UNIQUE INDEX "JOHNY"."SYS_C0013353" ON "JOHNY"."JOHNY_TABLE" ("ID")
- PCTFREE 10 INITRANS 2 MAXTRANS 255
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
- FAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "JOHNY_TABSPACE"
- SQL> select Username from dba_users
- 2 ;
- USERNAME
- ------------------------------
- SYS
- SYSTEM
- ANONYMOUS
- APEX_PUBLIC_USER
- FLOWS_FILES
- APEX_040000
- OUTLN
- DIP
- ORACLE_OCM
- XS$NULL
- MDSYS
- USERNAME
- ------------------------------
- CTXSYS
- DBSNMP
- XDB
- APPQOSSYS
- HR
- 16 rows selected.
- SQL> create user testdb identified by password;
- User created.
- SQL> select username from dba_users;
- USERNAME
- ------------------------------
- TESTDB
- SYS
- SYSTEM
- ANONYMOUS
- APEX_PUBLIC_USER
- FLOWS_FILES
- APEX_040000
- OUTLN
- DIP
- ORACLE_OCM
- XS$NULL
- USERNAME
- ------------------------------
- MDSYS
- CTXSYS
- DBSNMP
- XDB
- APPQOSSYS
- HR
- 17 rows selected.
- SQL> grant create session to testdb;
- Grant succeeded.
- SQL> create tablespace testdb_tablespace
- 2 datafile 'testdb_tabspace.dat'
- 3 size 10M autoextend on;
- Tablespace created.
- SQL> create temporary tablespace testdb_tablespace_temp
- 2 tempfile 'testdb_tabspace_temp.dat'
- 3 size 5M autoextend on;
- Tablespace created.
- SQL> drop user testdb;
- User dropped.
- SQL> create user testdb
- 2 identified by password
- 3 default tablespace testdb_tablespace
- 4 temporary tablespace testdb_tablespace_temp;
- User created.
- SQL> grant create session to testdb;
- Grant succeeded.
- SQL> grant create table to testdb;
- Grant succeeded.
- SQL> grant unlimited tablespace to testdb;
- Grant succeeded.
- SQL>
- CREATE USER auto_exchange IDENTIFIED BY 123456;
- GRANT RESOURCE TO auto_exchange;
- GRANT CONNECT TO auto_exchange;
- GRANT CREATE VIEW TO auto_exchange;
- GRANT CREATE SESSION TO auto_exchange;
- GRANT UNLIMITED TABLESPACE TO auto_exchange;
Add Comment
Please, Sign In to add comment