oracle databases server
create database TEST
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\oraclexe\app\oracle\oradata\TEST\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘C:\oraclexe\app\oracle\oradata\TEST\REDO02.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE’C:\oraclexe\app\oracle\oradata\TEST\SYSTEM.DBF’ size 100m autoextend on
sysaux datafile ‘C:\oraclexe\app\oracle\oradata\TEST\SYSAUX.DBF’ size 100m autoextend on
undo tablespace undotbs1 datafile ‘C:\oraclexe\app\oracle\oradata\TEST\UNDOTBS1.DBF’ size 100m autoextend on
CHARACTER SET AL32UTF8
;
CREATE DATABASE axisdevdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
GROUP 1 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo01_a.log’) SIZE 100M
GROUP 2 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo02_a.log’) SIZE 100M
GROUP 3 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo03_a.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/oracle/oradata/axisdevdb/Disk1/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/axisdevdb/Disk1/sysaux01.dbf’ SIZE 325M REUSE
SIZE 20M REUSE
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Log into the database server as a user belonging to ‘dba’ [unix ] or ‘ora_dba’ [windows ] group , typically ‘oracle’, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:
$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:32:09 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
SQL> show user
USER is “SYS”
SQL> passw system
Changing password for system
New password:
Retype new password:
Password changed
SQL> quit
Next, we need to change the password of SYS:
$ sqlplus “/ as system”
SQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:36:45 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name: system
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
SQL> passw sys
Changing password for sys
New password:
Retype new password:
Password changed
SQL> quit
You should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.
Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)
Stop the Oracle service of the instance you want to change the passwords of.
Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
Create a new pwd file by issuing the command:
orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.
Oracle default user and password
sqlplus /nolog
conn /as sysdba
alter user system identified by manager
OK, you can now use the Oracle database service normally. Specific instructions and points of attention are given in the next section.
Extra operation
1, modify the instance name
select instance from v$thread;
echo $ORACLE_SID
First, create a user
create user test identified by password
alter user test identified by password ;
Second, the authorization role
grant connect, resource to test;
revoke connect, resource from test;
Second, delete the user
drop user test;
drop user test cascade;
Fourth, create / authorize / delete the role
create role testRole;
grant select on class to testRole;
drop role testRole;
test:
grant resource to nova; //
Role related
Fifth, create wm_concat function on 12C
11g2 and 12C have abandoned the wm_concat function
Create wm_concat function on 12C Oracle default user name and password cheat sheet
create user ums_dev identified by ums_dev?
grant session, connect, resource to ums_dev;
ALTER USER ums_dev ACCOUNT UNLOCK;
alter user ums_dev identified by ums_dev;
grant unlimited tablespace to ums_dev;
alter table table_user add (overdate TIMESTAMP(6));
View Oracle Role Users and Permissions
select user_id, username, DEFAULT_TABLESPACE, ACCOUNT_STATUS,PROFILE from dba_users;
select username,default_tablespace from user_users;
select * from user_role_privs;
select * from user_sys_privs
select * from user_tab_privs
Check the table related information
select sum(bytes)/(1024*1024) tablesize from user_segments where segment_name=’ZW_YINGYEZ’;
select index_name,index_type,table_name from user_indexes order by table_name
select * from user_ind_columns where table_name=’CB_CHAOBIAOSJ201004′
select sum(bytes)/(1024*1024) as indexsize from user_segments
where segment_name=upper(‘AS_MENUINFO’)
select * from v$version
Import and Export
exp dbserver/dbserver1234@ORCL file=/opt/dbserver.dmp owner=dbserver
imp dbserver/dbserver1234@XE file=c:\orabackup\full.dmp log=c:\orabackup\imp.log full=y
Export
exp dbserver/dbserver1234@ORCL file=dbserver.dmp log=dbserver.log owner=ums rows=n
exp dbserver/dbserver1234 jdbc:oracle:thin:@10.10.73.206:1521:umpay file=dbserver.dmp log=dbserver.log owner=ums rows=n
Recent Comments