YW
发帖数: 46 | 1 Hi Friends,
I am a new DBA wondering if somebody can help me on the following questions.
I am using Oracle 11g. I created a new database in the server, and I used
dbconsole to create the tablespace for this new database. it turns out that
the tablespace connected to the existing database instead of the new
database.
1. What steps or settings did I miss when using dbconsole?
2. Should I use Create tabalespace command instead of dbconsole, since it's
easy to controll?
3. Since I have to drop the t... 阅读全帖 |
|
c*****d
发帖数: 6045 | 2 1. 如果是在windows上的db console，需要在注册表修改oracle_sid
2. 我基本不用图形界面，用command更方便快捷
3. 在sqlplus里运行drop tablespace tablespace_name;
如果tablespace里已经存在segment，并且你确认这些table没用，可以用drop
tablespace tablespace_name including contents |
|
q*****e
发帖数: 6 | 3 它跟database是什么关系？一个DB可以包括若干tablespaces？然后每个tablespace再包
涵若干tables？这个概念是为了什么提出来的啊？dummy questions，sorry。
谢谢。 |
|
YW
发帖数: 46 | 4 Dear Coolbid,
Thank you very much for your response. Now I know what I did wrong was when
I opened dbconsole to create tablespace, I used the original database URL,
in https//hostname:XXXX/em ( I believe this is called 注册表 in Chinese,
right?), the XXXX number is for original dabase, so it points to the orginal
database. I fixed it to delete the tablespace created in original database,
then recreated it in new database. It should be fine now. I don't have tool
setup yet, so can't use command at t... 阅读全帖 |
|
y*****g
发帖数: 677 | 5 现在大家硬盘大了，转的快了，也便宜了。 大家开始用 bigfile tablespace
to simplify tablespace management ??
有没有什么 performance issue? |
|
s****d
发帖数: 13 | 6 drop tablespace tab_name |
|
m****a
发帖数: 2 | 7 try this:
select tablespace_name,file_name,status from dba_data_files;
if the datafiles you deleted still shows "AVILABLE"
you need to do this for each of those files:
alter database data file 'filename' offline;
then you can do
drop tablespace tablespacename. |
|
h****p
发帖数: 25 | 8 big tablespace优势：
1. 简化管理，这个不多说了。
2. 由于数据库里数据文件数目是有限的，所以使用bigtablespace会比SMT拥有一个更
高的数据库总容量,很极端的场景需要这个特性。
performance issue，其实并不在于文件多少，主要是存储规划等等。
恢复时间只要不是重建整个文件就还好，而且海量库的备份恢复策略完全不同于普通数
据库，
简单的全备+增量是不行的，这是另外一个话题了。
总的来说，适用于数据增量以及总量很大的场景。 |
|
z******4
发帖数: 4716 | 9 数据库结构
加入同学们，上面都熟悉了，简单SQL会写了，那么下面要稍微深入数据库学习了，这
段，我们来学习数据库结构，记住，同学们都要熟悉背住这些名词，面试必备啊，不会
的话，肯定完蛋
前面，我们用Excel创建了一个简单初级数据库，哪实际的数据库哪，当然不会那么简单
我们来看这个图
一步步来，先看最底下一层
Data files
好了，我前面指出一个excel文件就像一个tablespace，什么是tablespace哪，就是这
些表都在这个tablespace里面
哪我又十几个excel文件，是不是会有十几个tablespace，是的，一般一个tablespace
会建在一个datafile里面
，哪一般oracle数据库在创建时会有几个data file哪
system
user
temp
redo
好了，你发现，你的头脑开始糊涂了吧，这些datafile 妈的和excel到底有什么关系
举例，user data file 里面可以创建user1 tablespace，user2 tablespace,每个
tablespace就相当于一个excel文件
简化的意思，这些data... 阅读全帖 |
|
l******b
发帖数: 39 | 10 lz看这个能成马?
Composite Partitioning
Composite partitioning partitions data using the range method, and within
each partition, subpartitions it using the hash or list method.
Composite range-list partitioning provides the manageability of range
partitioning and the explicit control of list partitioning for the
subpartitions.
Composite Partitioning Range-List Example
CREATE TABLE bimonthly_regional_sales
(deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2))
PARTIT... 阅读全帖 |
|
f********t
发帖数: 74 | 11 I don't think you understand tablespace correctly. tablespaces are used to
organize the datafiles. One datafile can belong to only one tablespace,
while a tablespace can have many datafiles. It is one of the logic structure of the database. A tablespace is not a database and it is not eual to schema either.
You can make a tablespace read only by using ALTER TABLESPACE command.
If you have not created myjunktable, it does not exist for sure.
CREATE USER command should work fine. |
|
c*****d
发帖数: 6045 | 12 就是这么一个场景：
oracle db，每个tablespace都有备份
星期三加了一个tablespace，但是忘记在备份脚本里加上这个tablespace
星期四业务继续，新的tablespace上创建了表，索引及相关ins/upd/del
星期五db损坏，发现少一个tablespace的备份 |
|
n****f
发帖数: 905 | 13
你不一定有这个权限。所以告诉你也不一定有用。
CREATE USER A
IDENTIFIED BY your_password_a
DEFAULT TABLESPACE example_a
QUOTA 10M ON example_a
TEMPORARY TABLESPACE temp
PROFILE app_user
PASSWORD EXPIRE;
CREATE USER B
IDENTIFIED BY your_password_b
DEFAULT TABLESPACE example_b
QUOTA 10M ON example_b
TEMPORARY TABLESPACE temp
PROFILE app_user
PASSWORD EXPIRE; |
|
j****s
发帖数: 271 | 14 ORA-01552: cannot use system rollback segment for non-system
tablespace [name]
Cause: An attempt was made to use the SYSTEM rollback segment for
operations
involving a non-system tablespace.
Action: Create one or more rollback segments and then use ALTER
ROLLBACK
SEGMENT ’name’ online. It may be necessary to modify the
initialization
parameter ROLLBACK_SEGMENTS to acquire one of the new private
rollback segments. Before creating a rollback segment outside the
SYSTEM
tablespace, it is necessary t |
|
y********o
发帖数: 2565 | 15 MySQL里面有不同的databases, 比如你可以 show databases 得到一个数据库名称列表
。在Oracle里面与此相对应的是叫做tablespace吗？
假设我是DBA, 对数据库myTestDB有绝对权限。我想创建几个其他用户，让他们对
myTestDB只有read only权限。
我这样试了一下：
create user johndoe
default tablespace myTestDB
identified by thepassword
temporary tablespace temp;
然后我说
grant create session to johndoe;
grant select on myjunktable to johndoe;
grant select on anotherjunktable to johndoe;
结果不对，好像johndoe被另立门户成了一个独立的database。瞧：
SQL> desc myjunktable;
ERROR:
ORA-04043: object myjunktable does not exist
看 |
|
z***y
发帖数: 7151 | 16 Suppose you get error from email:
18725760 ORA-1654: unable to extend index SURROUND.SAMPLEBI_0 by 6400
in tablespace SURROUNDINDX1
18725762 ORA-1654: unable to extend index SURROUND.SAMPLEBI_0 by 6400
in tablespace SURROUNDINDX1
18725771 ORA-1654: unable to extend index SURROUND.SAMPLEBI_0 by 6400
in tablespace SURROUNDINDX1
1. Tell me how to implement such alert?
2. How do you handle such alert？
思路很重要， 答案不是唯一地。 嗯， 这是真实的一个案例， 这个数据库有2T那么大
。 |
|
m******g
发帖数: 91 | 17 login as sysdba (sys)
create tablespaces for this user;
create a profile for this user;
create new user with relevant tablespace and profile;
grant privileges and roles (better start with a dba role) |
|
s***m
发帖数: 28 | 18
You may want to create a database first before you create any user. Login as
dba create database, then create user with proper privilege in your database.
On Orace machine
sqlplus /nolog;
connect system as sysdba;
######Then create your user#############3
create user yourusername identified by 'password';
grant connect to yourusername;
grant resource to yourusername;
You need to setup appropriate tablespace for your user so user will not put
junk into system tablespace. |
|
s***m
发帖数: 28 | 19 I am trying to export a production oracle database schema of a tablespace to a
test system. However, I have limited space on test system. On production
system, many data files are created for different indexes and tables. These
datafile is created using large disk spaces. How can I export the production
tablespace schema but with smaller datafile size definition?
Thanks |
|
l***r
发帖数: 459 | 20 想用CATALOG但又想省事.不知道这样行不行:
In the same instance as production database, create a seperate tablespace and
create RMAN catalog inside that new tablespace. The whole production database
have user-managed backup.
Any serious problem?
Thanks! |
|
y********o
发帖数: 2565 | 21 Thanks and yes, I don't know what exactly tablespace is.
Also, I am only interested in creating users who have read only permissions
on all tables. I am not interested in creating read-only tables/datafiles/
tablespaces per se.
Can you share some of your wisdom please? |
|
B*****g
发帖数: 34098 | 22 unix shell
or
Data Pump Import Wizard
Note: This Toad feature is only available in the commercial version of Toad in either the Professional Edition or with the optional Quest DBA Module.
The Data Pump Import wizard lets you use easily use Oracle's Data Pump to import data into the database on a large scale.
There are five import modes to the Data Pump:
• Full Import
• Schema
• Table
• Tablespace
• Transportable Tablespace
The Toad wizard supports all of these, and |
|
Q*******1
发帖数: 91 | 23 怎么确定是否被学校坑了。给你看一个学校的课程表，你看看会不会被坑呢。
%%%%%%%%%%%%%%%这个是bgsu的课程设置
Fall
MSA 5020 Regression Analysis
MSA 5400 Database Management
MSA 5470 Exploratory Data Analysis
MSA 6010 Decision Optimization
MSA 6701 Analytics Project I
Spring
MSA 5160 Time-Series Analysis and Forecasting
MSA 5600 Business Intelligence
MSA 6440 Data Mining
MSA 6500 Big Data Analytics
MSA 6702 Analytics Project II
Summer
MSA 6450 Advanced Data Analytics
MSA 6600 Project Management
MSA 6703 Analytics Project III
%%%%%%%%%%%%%这个是S... 阅读全帖 |
|
Q*******1
发帖数: 91 | 24 怎么确定是否被学校坑了。给你看一个学校的课程表，你看看会不会被坑呢。
%%%%%%%%%%%%%%%这个是bgsu的课程设置
Fall
MSA 5020 Regression Analysis
MSA 5400 Database Management
MSA 5470 Exploratory Data Analysis
MSA 6010 Decision Optimization
MSA 6701 Analytics Project I
Spring
MSA 5160 Time-Series Analysis and Forecasting
MSA 5600 Business Intelligence
MSA 6440 Data Mining
MSA 6500 Big Data Analytics
MSA 6702 Analytics Project II
Summer
MSA 6450 Advanced Data Analytics
MSA 6600 Project Management
MSA 6703 Analytics Project III
%%%%%%%%%%%%%这个是S... 阅读全帖 |
|
j***a
发帖数: 10844 | 25 You are right. He mentioned many terms without explanation, assuming the
audience knows about them.
The tablespace is a complicated concept and requires one lesson to talk
about that.
Control file is another important file, which is very important.
Redo log is even more complicated.
On the other hand, it's really hard to give such a lecture without knowing
what kind of audience he will get. |
|
s******e
发帖数: 19 | 26 i am now using the oracle database,
and i happen to meet this question,
who can solve it!
the error is :
cannot use system rollback segment for non-system tablespace 'USERS' |
|
S**a
发帖数: 120 | 27 I'm using Oracle 8.1.6 on NT 4 server.
I EXP a table which has a CLOB field from one database and
try to IMP it to another database, but failed. the error
messages showes the tablespace of the table is not existed
in the new database, I already set IGNORE = Yes, but still
this problem.
Some suggestions ?
Thanks. |
|
c****v
发帖数: 2 | 28 在自己建的tablespace里，用着用着就会多出好多莫名其妙的表来，不只是干什么用的。
能不能删掉？表名如下：
SMP_xxx （例如：SMP_JOB_, SMP_SERVICES)
EVT_xxx (例如：EVT_DEST_PROFILE,EVT_INSTANCE)
SMXXX (例如：SMAGENTJOB_S, SMARCHIVE_S) |
|
p****s
发帖数: 3184 | 29
select username from all_users;
offers you all user's names
apply count() aggregate will tell you how many they are
select username, default_tablespace from user_users
offers you default tablespace associated with a normal user
select username, default_tablespace from dba_users;
is similar to the above, but tells you same thing about those
dbas |
|
b*******t
发帖数: 56 | 30 创建了一个user之后是不是要create database 再create table?还是直接可以create
table?如果直接可以create table 那么database和table的关系是什么？
什么是tablespace 呀？
Thanks. |
|
o**n
发帖数: 14 | 31 the associated datafile was deleted accidentally, |
|
o**n
发帖数: 14 | 32 because the datafile was deleted, can't drop it that way. |
|
f***e
发帖数: 17 | 33 谢谢大家的指导。
我log in 后用下面的command 对吗？
SQL> CONNECT SYS/change_on_install AS SYSDBA
然后用什么command 去 create tablespace? |
|
l***r
发帖数: 459 | 34 【 以下文字转载自 Programming 讨论区,原文如下 】
发信人: laoer (xiaoer), 信区: Programming
标 题: question on SQL
发信站: The unknown SPACE (Wed May 28 08:34:21 2003) WWW-POST
I wonder how I can show actual usage for a tablespace by SQL command line? I
knew OEM can display percentage.
Thanks! |
|
s*******e
发帖数: 151 | 35 Schema did take space sometimes. especially tablespace.
to
These
production |
|
n********a
发帖数: 68 | 36 How much as compared to data?
Add up the bytes taken by your SYSTEM tablespace. |
|
s***m
发帖数: 28 | 37 Yes, I want to import full database schema from production system to test
system. However, I don't want to import any of the data. I just need the
tablespace definition, trigger, storedproc, functions and constraints. Owing
to the production system's size, at which many datafiles has been created for
userspace and index space, my export captures the size of the those index
datafiles. If you have a documention on how to do this, it will be very
helpful.
Thanks
test
before
to
These
production |
|
h******i
发帖数: 133 | 38 EXPORT 的CONTROL FILE里不是有一个PARAMETER叫ROWS，假如你设为N的话，
那么你只EXPORT TABLE 的DEFINITION， 而不会包含DATA？
你是想问这个吗？
OracleSQLPLUS的资料里有一章就叫EXPORT，做你读一下就全懂了。。。
for
tablespace |
|
z***y
发帖数: 7151 | 39 union all 会导致merge scan, 不必要的使用tempdb tablespace.
1 might be better.
ITEM,
all |
|
w*******e
发帖数: 1622 | 40 首先, 俺是Oracle新手哈...问啥stupid的问题表笑.....
在Oracle中, whole database backup里, 都有什么被backup了?
我在backup folder里看到: 文件名乱七八糟的两files.
看了看Oracle说明, 好象有datafiles, redo log, spfile and control files. (是不
是应该有四个backup的file呢?)
问这个问题是因为更大的一问题:
比如我现在有那个whole database backup files(两个), 如果这时我的机器坏了, 我
怎么来restore from those two files呢? 就是在我install了OS和Oracle software后
, 怎么做? 能不能详细点?? 谢谢....(看website好象只有recover单个tablespace和
table什么的) |
|
c*****d
发帖数: 6045 | 41 os backup的话,原文件有几个,备份文件就有几个
原文件多大,备份文件就有多大
rman backup的话,rman只备份实际的数据
并且可以将多个tablespace(相当于sql server中的database)备份在一个backupset里
我估计你的是rman backup,恢复的时候要用rman来恢复 |
|
s****y
发帖数: 581 | 42 是不是要用RMAN作备份，Oracle DB 必须在ARCHIVELOG mode状态下运行？
无论Datafile， controlfil／spfile， full database， or tablespace？ |
|
c*****d
发帖数: 6045 | 43 oracle里一个tablespace(对应于sql server中的database)
如果没有备份，但是有从创建到delete这段时间所有的log
是可以恢复的
举个例子，比如创建的时候是状态0
log里记录了状态0-1-2-3-4-5的变化
在状态5的时候table被删除
如果有0的备份, apply 0-1-2-3-4的变化, stop, done
没有0的备份, 如果有办法产生一个空库, apply 0-1-2-3-4的变化, stop, done
it could be done in oracle, but I am not sure sql server |
|
m****d
发帖数: 372 | 44 不懂SQL server,sql server的databases是不是相当于oracle 的tablespace.在oracle
下，
800GB/150GB =5.3 -->需6块盘;raid 5 -> 那要7块盘了;其余是raid10 -> raid 5 8块盘
.剩下12块盘raid10，12/2 * 150=900GB
user
5 |
|
|
l******y
发帖数: 60 | 46 都是高手！cume_dist()我从来没用过 :-(
tt原来在的tablespace是啥——不会看呀
确认已打开了flashback——正解，确实没打开，但怎么打不开呢？我从dba账户也打不
开，为什么呢？
SQL> select log_mode, flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------------------ ------------------------------------
NOARCHIVELOG NO
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database |
|
c******n
发帖数: 7263 | 47 all_tables可以看tablespace信息 |
|
c******n
发帖数: 7263 | 48 bigfile除了可以搞贼大的db之外还有啥significant的好处？
ｄｂ 文件的数目是变少了，这个对于大型数据库的ｄｂａ来说，管理起来能省点事
但是其他可能会有ｐｅｒｆｏｒｍａｎｃｅ的问题，比如万一一个文件坏了，影响的ｄ
ａｔａ就很多，ｒｅｃｏｖｅｒ起来也会比较慢 |
|
n****e
发帖数: 1403 | 49 Make sense.
32K db_block maximum bigfile size is 128 T
8K db_block maximum bigfile size is 32T.
You mao yong a? Duo fen ji ge wen jian jiu wan le. |
|
y*****g
发帖数: 677 | 50 如果是8K的PAGESIZE， 1个文件最大32G。
1T的数据需要40个文件， 2T的数据就是80个。
一大堆零散的文件有些眼花吧 |
|
