关于Oracle 12c中的用户

Question

我已经在我的系统上安装了Oracle 12c数据库。我有一个需要访问数据库的应用程序。

在之前的Oracle11g中，我使用以下命令创建用户。

create user name identified by name;  
grant connect,create session,resource,create view to name;

有谁能告诉我如何根据我的上述要求在Oracle 12c中创建用户？我使用了以下语句，但我的安装显示了一个致命错误：

FATAL ERROR - java.sql.SQLException: ORA-01950: no privileges on tablespace 'USERS'

以下是使用的陈述。

create user c##test1 identified by test1 container = ALL;
grant connect,create session,resource,create view to test1;

Answer 1

试试这个：

#!/bin/bash
USERVALID=false;

if [ -z $1 ]; then
 echo -e "\nThis script will create a new common user in Oracle"
fi

while [[ $USERVALID == false ]]; do
 if [ -z $1 ]; then
  echo -e "Username must start with 'c##' or 'C##'\n"
  read -p "Enter a username: " NEWUSER
 else
  NEWUSER=$1
 fi
 if [[ $NEWUSER == c##* ]]
 then
  USERVALID=true;
 elif [[ $NEWUSER == C##* ]]
 then
  USERVALID=true;
 else
  USERVALID=false;
  echo -e "\nInvalid username";
  if [ ! -z $1 ]; then
   echo -e "Username must start with 'c##' or 'C##'\n"
   exit 0
  fi
 fi
done

while [[ -z $NEWPWD ]]; do
 if [ -z $2 ]; then
  read -p "Enter a password: " NEWPWD
 else
  NEWPWD=$2
 fi
 if [[ -z $NEWPWD ]]
 then
  echo -e "Password must not be NULL"
 fi
done

get_user_status () {
 sqlplus -s / as sysdba <<!
 set heading off
 set feedback off
 set pages 0
 select username from all_users where username = '${NEWUSER^^}';
!
}

USERDROP=$(get_user_status)
if [[ $USERDROP =~ ${NEWUSER^^} ]]; then
 echo -e "\nUser already exists...\nAttempting to drop user\n"
 echo -e "DROP USER $NEWUSER CASCADE;" | sqlplus -s / as sysdba
 USERDROP=$(get_user_status)
 if [[ $USERDROP =~ ${NEWUSER^^} ]]; then
  echo -e "Please ensure user is disconnected from the database before proceeding.\n"
  exit 0
 fi
fi

echo -e "CREATE USER $NEWUSER IDENTIFIED BY $NEWPWD;"  | sqlplus -s / as sysdba

USERDROP=$(get_user_status)
if [[ ! $USERDROP =~ ${NEWUSER^^} ]]; then
 echo -e "You are a bad person.\n"
 exit 0
fi

echo -e "GRANT CREATE SESSION TO $NEWUSER;"            | sqlplus -s / as sysdba
echo -e "GRANT CREATE PROCEDURE TO $NEWUSER;"          | sqlplus -s / as sysdba
echo -e "GRANT CREATE SEQUENCE TO $NEWUSER;"           | sqlplus -s / as sysdba
echo -e "GRANT CREATE DATABASE LINK TO $NEWUSER;"      | sqlplus -s / as sysdba
echo -e "GRANT CREATE TABLE TO $NEWUSER;"              | sqlplus -s / as sysdba
echo -e "GRANT CREATE VIEW TO $NEWUSER;"               | sqlplus -s / as sysdba
echo -e "GRANT CREATE MATERIALIZED VIEW TO $NEWUSER;"  | sqlplus -s / as sysdba
echo -e "GRANT QUERY REWRITE TO $NEWUSER;"             | sqlplus -s / as sysdba
echo -e "GRANT SELECT ANY TABLE TO $NEWUSER;"          | sqlplus -s / as sysdba
echo -e "GRANT SELECT ON SYS.V_\$SESSION TO $NEWUSER;" | sqlplus -s / as sysdba
echo -e "GRANT EXECUTE ON SYS.DBMS_LOCK TO $NEWUSER;"  | sqlplus -s / as sysdba
echo -e "GRANT UNLIMITED TABLESPACE TO $NEWUSER;"      | sqlplus -s / as sysdba

echo -e "\nAll done!\n"

Answer 2

您还应该为用户的默认表空间分配配额：

CREATE USER name
IDENTIFIED BY name
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 50M /* or any other number that makes sense */ ON users

GRANT CONNECT, CREATE SESSION, RESOURCE, CREATE VIEW TO name;

Answer 3

最佳实践是创建一个表空间并将其分配给用户。

为了便于理解，请对用户名和表空间使用相同的名称

CREATE BIGFILE table SPACE C##1
DATAFILE'/path/to/DATAFILE/C##1.dbf'
SIZE 10M自动扩展到下一个5M MAXSIZE UNLIMITED
区段管理本地段空间管理自动取消记录

CREATE USER C##1
IDENTIFIED BY密码默认表空间C##1
QUOTA UNLIMITED ON C##1；